[Phred]

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

[tealady]

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.

[soulboy77]

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.

[Phred]

probably better give an example. I used Current and next to simplify(?) first post. The actual periods are based on YYYYMM ;

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 .

[perkin77]

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")

[platelet]

I think last Saturday of the month that A2 is in is...

=EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)

[platelet]

Originally Posted by platelet

I think last Saturday of the month that A2 is in is...

=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

[perkin77]

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

[tealady]

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.

[jsmith99]

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.

[tealady]

It's all in #4.

[jsmith99]

Originally Posted by tealady

It's all in #4.

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'.

[Phred]

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!

[platelet]

Originally Posted by jsmith99

...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?
...

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,

[jsmith99]

Originally Posted by platelet

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.