Last Working Day of Month

    Hi all,


    Have the following formula. It works if the last working day is Monday thru Friday, however bombs out if the last day of the month is a Saturday or Sunday. Am not at all sure how I can fix the formula to work correctly.


    FYI ... $D$1 = `=DATE(YEAR(NOW()),MONTH(NOW())+3,1)`
    Which returns 01/12/2005 00:00.


    =IF(TEXT(EOMONTH($D$1,0),"ddd")="Mon",TEXT(EOMONTH($D$1,0),"dd-mmm-yy ")&" "&TEXT(TIME(12,0,0),"hh:mm "),IF(TEXT(EOMONTH($D$1,0),"ddd")="Tue",TEXT(EOMONTH($D$1,0),"dd-mmm-yy ")&" "&TEXT(TIME(12,0,0),"hh:mm "),IF(TEXT(EOMONTH($D$1,0),"ddd")="Wed",TEXT(EOMONTH($D$1,0),"dd-mmm-yy ")&" "&TEXT(TIME(12,0,0),"hh:mm "),IF(TEXT(EOMONTH($D$1,0),"ddd")="Thu",TEXT(EOMONTH($D$1,0),"dd-mmm-yy ")&" "&TEXT(TIME(12,0,0),"hh:mm "),IF(TEXT(EOMONTH($D$1,0),"ddd")="Fri",TEXT(EOMONTH($D$1,0),"dd-mmm-yy ")&" "&TEXT(TIME(12,0,0),"hh:mm "),IF(TEXT(EOMONTH($D$1,0),"ddd")="Sat",TEXT(EOMONTH($D$1,0),"ddd"),"ERR"))))))


    The above formula is still being hacked by myself.


    I had based it on the following formula for the first working day of the month:
    =IF(TEXT($D$1+0,"ddd")="Mon",TEXT($D$1+0.3333333,"dd-mmm-yy hh:mm "),IF(TEXT($D$1+0,"ddd")="Tue",TEXT($D$1+0.3333333,"dd-mmm-yy hh:mm "),IF(TEXT($D$1+0,"ddd")="Wed",TEXT($D$1+0.3333333,"dd-mmm-yy hh:mm "),IF(TEXT($D$1+0,"ddd")="Thu",TEXT($D$1+0.3333333,"dd-mmm-yy hh:mm "),IF(TEXT($D$1+0,"ddd")="Fri",TEXT($D$1+0.3333333,"dd-mmm-yy hh:mm "),IF(TEXT($D$1+1,"ddd")="Mon",TEXT($D$1+1.3333333,"dd-mmm-yy hh:mm "),IF(TEXT($D$1+2,"ddd")="Mon",TEXT($D$1+2.3333333,"dd-mmm-yy hh:mm "),"ERR")))))))


    Thanks in advance for any assistance.

    Re: Last Working Day of Month


    the formula


    Code
    =IF(WEEKDAY(EOMONTH(D12,0),2)=7,EOMONTH(D12,0)-2,IF(WEEKDAY(EOMONTH(D12,0),2)=6,EOMONTH(D12,0)-1,EOMONTH(D12,0)))


    will return the last working day of the month....if thats what you want (D12 is the cell where your reference date is located)


    you may further combine it with WORKDAY function for greater accuracy (if the last friday is a declared holiday)



    HTH


    pangolin

    Re: Last Working Day of Month


    Huge thanks - seems I really wend down the wrong path with that formula


    I could use a similar version for the start of month I guess too ?


    Again, many thanks.


    The amended formula:
    =IF(WEEKDAY(EOMONTH($D$1,0),2)=7,TEXT(EOMONTH($D$1,0)-2,"dd-mmm-yy ")&"17:00 ",IF(WEEKDAY(EOMONTH($D$1,0),2)=6,TEXT(EOMONTH($D$1,0)-1,"dd-mmm-yy ")&"17:00 ",TEXT(EOMONTH($D$1,0),"dd-mmm-yy ")&"17:00 "))

    Re: Last Working Day of Month


    This one may be a little more elegant:


    =WORKDAY(EOMONTH($D$1,0),IF(WEEKDAY(EOMONTH($D$1,0),2)>5,1,0))


    The key comes in using return type #2 on the weekday formula rather than the default.

    Re: Last Working Day of Month


    Quote from dtully;589640

    =WORKDAY(EOMONTH($D$1,0),IF(WEEKDAY(EOMONTH($D$1,0),2)>5,1,0))


    Is that supposed to give last workday in the month? Surely it doesn't, what if D1 is today?


    This thread is more than 6 years old so I'm not sure whether Cameron is still interested but this is simplest if you use WORKDAY to go back 1 working day from the 1st of the next month, i.e.


    =WORKDAY(EOMONTH($D$1,0)+1,-1)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login