Options
One for Excel Function experts
Phred
Posts: 1,147
Forum Member
✭✭✭
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 built-in functions.
Many thanks
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 built-in functions.
Many thanks
0
Comments
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.
Say dates are
2014-04-24 (Thu) - period would be 201404
2014-04-25 (Fri) - period would be 201404
2014-04-26 (Sat) - period would be 201404
2014-04-27 (Sun) - period would be 201405
2014-04-28 (Mon)- period would be 201405
2014-04-29 (Tue)- period would be 201405
2014-04-30 (Wed)- period would be 201405
2014-05-01(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 .
=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")
=EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)
Though I didn't check that to see what happens if the EOM or A2 is Saturday
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
Personally, I would create a table in the spreadsheet with each date in it and what period. Easier to follow and maintain.
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.
You're right; I read the thread too quickly, and assumed that was a suggested answer.
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'.
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!
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,
Thanks, in that case the table should consist of the first sunday in a period.
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.