

One for Excel Function experts 

Thread Tools  Search this Thread 
07022013, 13:11  #1 
Forum Member
Join Date: Jun 2006
Posts: 1,119

One for Excel Function experts
This has been driving me mad for a couple of days, so please can anyone help?
Need to look at a date, in, for example, A2, and if it is on or before the last saturday in a month return "Current period", otherwise "Next period" in G2. I have tried various if() statements using day), eomonth() weekday() etc, but can't hit on the correct formula to get every date correct. I can get this to work using VBA, but unfortunately can't use VBA in live system, so must use Excel builtin functions. Many thanks 
Please sign in or register to remove this advertisement.

07022013, 13:28  #2 
Forum Member
Join Date: Apr 2005
Location: colchester
Posts: 11,121

I would look at using a vlookup. I'd add a table with period/month and a list of the last saturdays and use that.
Hard to say more without seeing more of what you are doing, as current and next are only valid at a certain point, then they become past. 
07022013, 13:59  #3 
Forum Member
Join Date: Jul 2005
Location: Herts
Posts: 14,674

A bit over complicated to do without creating a list of financial period dates to do a vlookup. Think about using a period number so the date will always refer to the correct period as time moves on.

07022013, 14:28  #4 
Forum Member
Join Date: Jun 2006
Posts: 1,119

probably better give an example. I used Current and next to simplify(?) first post. The actual periods are based on YYYYMM ;
Say dates are 20140424 (Thu)  period would be 201404 20140425 (Fri)  period would be 201404 20140426 (Sat)  period would be 201404 20140427 (Sun)  period would be 201405 20140428 (Mon) period would be 201405 20140429 (Tue) period would be 201405 20140430 (Wed) period would be 201405 20140501(Thu) period would be 201405 Note Period ends on last Saturday of month The sheet lists all dates for a 5 year period (backwards and forwards from current day) and maps days to different process details across the company. All other mappings are done automatically, it is just this one I can't sort out . 
07022013, 16:21  #5 
Forum Member
Join Date: Aug 2009
Posts: 3

I think this should work for any date
=YEAR(A2)&TEXT(MONTH(A2)+IF(A2<=(EOMONTH(A2,0)WEEKDAY(EOMONTH(A2,0),1)+IF(WEEKDAY(EOMONTH(A2,0),1)=7,7,0)),0,1),"00") 
07022013, 16:34  #6 
Forum Member
Join Date: May 2002
Location: GL51 0EX
Posts: 7,675

I think last Saturday of the month that A2 is in is...
=EOMONTH(A2,0)WEEKDAY(EOMONTH(A2,0),1) 
07022013, 17:10  #7 
Forum Member
Join Date: May 2002
Location: GL51 0EX
Posts: 7,675

Quote:
I think last Saturday of the month that A2 is in is...
=EOMONTH(A2,0)WEEKDAY(EOMONTH(A2,0),1) 
07022013, 18:04  #8 
Forum Member
Join Date: Aug 2009
Posts: 3

EOM bit gives the last day of the month for date in A2
Weekday bit returns day of week for last day of month. Sun is 1, Mon is 2 etc The third bit returns 7 if the last day is Saturday otherwise 0. This is needed to handle the last day month being Sat Therefore all three bits together give the last Sat in the month and the IF statement returns a 1 if the date in A2 is after the last Sat 
07022013, 18:08  #9 
Forum Member
Join Date: Apr 2005
Location: colchester
Posts: 11,121

Strikes me that if the period is that important across the company, then there should be a table in the main application that holds what period a date is in.
Personally, I would create a table in the spreadsheet with each date in it and what period. Easier to follow and maintain. 
07022013, 20:43  #10 
Forum Member
Join Date: Apr 2005
Posts: 14,673

I think the OP needs to be a bit more specific about the meaning of " if it is on or before the last saturday in a month ".
Does this mean the current month? Or the month in which the date occurrs? i.e. does it mean between the 1st and last saturday of the current month, or between the 1st and last saturday of the month in which the date occurs. e.g. a date of Feb 20th is before the last saturday of february. 20th of march is after the last saturday of february (the current month) but before the last saturday of march (the month to which the date relates). Strictly speaking, any date you can come up with is after the last saturday of a month. 
07022013, 21:48  #11 
Forum Member
Join Date: Apr 2005
Location: colchester
Posts: 11,121

It's all in #4.

08022013, 00:27  #12 
Forum Member
Join Date: Apr 2005
Posts: 14,673

Quote:
It's all in #4.
If you do a vlookup to a table, and the actual value isn't present, does it 'drop through' to the next value which is? If so, then you simply need a table of all last Saturdays in successive months. The decode is then YYYYMM of the key. If a value is present, the vlookup will find it, and return its month number. If not there, it'll find the next 'last Saturday'. 
08022013, 09:32  #13 
Forum Member
Join Date: Jun 2006
Posts: 1,119

Hi folks,
Thanks for all the suggestions. I eventually solved it using: =IF(WEEKDAY(EOMONTH(A2,0))=7,TEXT(A2,"YYYYMM"),IF(DAY(A2)WEEKDAY(A2)<DAY(EOMONTH(A2,0))6,TEXT(A2,"YYYYMM"),TEXT(A2+8,"YYYYMM"))) The suggestion from Perkin77 seemed to work for vast majoriy of dates, but there were a few over the five year period that did throw anomolies, but was useful in that it made me start thinking in a different way to what I had originally became fixated on! 
08022013, 10:53  #14 
Forum Member
Join Date: May 2002
Location: GL51 0EX
Posts: 7,675

Quote:
...If you do a vlookup to a table, and the actual value isn't present, does it 'drop through' to the next value which is?
... TRUE will look for the largest value that is below the value you're looking up, 
08022013, 11:57  #15 
Forum Member
Join Date: Apr 2005
Posts: 14,673

Quote:
Theres a TRUE/FLASE flag on VLOOKUP, FALSE requires an exact match,
TRUE will look for the largest value that is below the value you're looking up, Thirty odd years ago I used to write programs involving tables to assign department names based on ranges of cost centre or account code, or even a combination of both. There were literally thousands of possible values. 
Thread Tools  Search this Thread 

All times are GMT. The time now is 02:18.