Digital Spy

Search Digital Spy
 

DS Forums

 
 

One for Excel Function experts


Reply
Thread Tools Search this Thread
Old 07-02-2013, 13:11
Phred
Forum Member
 
Join Date: Jun 2006
Posts: 1,119

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
Phred is offline   Reply With Quote
Please sign in or register to remove this advertisement.
Old 07-02-2013, 13:28
tealady
Forum Member
 
Join Date: Apr 2005
Location: colchester
Posts: 11,048
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.
tealady is offline   Reply With Quote
Old 07-02-2013, 13:59
soulboy77
Forum Member
 
Join Date: Jul 2005
Location: Herts
Posts: 14,572
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.
soulboy77 is online now   Reply With Quote
Old 07-02-2013, 14:28
Phred
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

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 .
Phred is offline   Reply With Quote
Old 07-02-2013, 16:21
perkin77
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")
perkin77 is offline   Reply With Quote
Old 07-02-2013, 16:34
platelet
Forum Member
 
Join Date: May 2002
Location: GL51 0EX
Posts: 7,591
I think last Saturday of the month that A2 is in is...

=EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)
platelet is offline   Reply With Quote
Old 07-02-2013, 17:10
platelet
Forum Member
 
Join Date: May 2002
Location: GL51 0EX
Posts: 7,591
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
platelet is offline   Reply With Quote
Old 07-02-2013, 18:04
perkin77
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
perkin77 is offline   Reply With Quote
Old 07-02-2013, 18:08
tealady
Forum Member
 
Join Date: Apr 2005
Location: colchester
Posts: 11,048
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.
tealady is offline   Reply With Quote
Old 07-02-2013, 20:43
jsmith99
Forum Member
 
Join Date: Apr 2005
Posts: 14,543
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.
jsmith99 is offline   Reply With Quote
Old 07-02-2013, 21:48
tealady
Forum Member
 
Join Date: Apr 2005
Location: colchester
Posts: 11,048
It's all in #4.
tealady is offline   Reply With Quote
Old 08-02-2013, 00:27
jsmith99
Forum Member
 
Join Date: Apr 2005
Posts: 14,543
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'.
jsmith99 is offline   Reply With Quote
Old 08-02-2013, 09:32
Phred
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!
Phred is offline   Reply With Quote
Old 08-02-2013, 10:53
platelet
Forum Member
 
Join Date: May 2002
Location: GL51 0EX
Posts: 7,591
...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,
platelet is offline   Reply With Quote
Old 08-02-2013, 11:57
jsmith99
Forum Member
 
Join Date: Apr 2005
Posts: 14,543
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.
jsmith99 is offline   Reply With Quote
 
Reply



Thread Tools Search this Thread
Search this Thread:

Advanced Search

 
Forum Jump


All times are GMT. The time now is 09:43.