One for Excel Function experts

PhredPhred 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

Comments

  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    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.
  • soulboy77soulboy77 Posts: 24,396
    Forum Member
    ✭✭✭
    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.
  • PhredPhred Posts: 1,147
    Forum Member
    ✭✭✭
    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 .
  • [Deleted User][Deleted User] Posts: 3
    Forum Member
    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")
  • plateletplatelet Posts: 26,358
    Forum Member
    ✭✭✭
    I think last Saturday of the month that A2 is in is...

    =EOMONTH(A2,0)-WEEKDAY(EOMONTH(A2,0),1)
  • plateletplatelet Posts: 26,358
    Forum Member
    ✭✭✭
    platelet wrote: »
    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
  • [Deleted User][Deleted User] Posts: 3
    Forum Member
    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
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    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.
  • jsmith99jsmith99 Posts: 20,382
    Forum Member
    ✭✭✭
    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.
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    It's all in #4.
  • jsmith99jsmith99 Posts: 20,382
    Forum Member
    ✭✭✭
    tealady wrote: »
    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'.
  • PhredPhred Posts: 1,147
    Forum Member
    ✭✭✭
    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!
  • plateletplatelet Posts: 26,358
    Forum Member
    ✭✭✭
    jsmith99 wrote: »
    ...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,
  • jsmith99jsmith99 Posts: 20,382
    Forum Member
    ✭✭✭
    platelet wrote: »
    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.
Sign In or Register to comment.