Formula to calculate number of working days left in month

    I have seen many posts on calculating the number of days between two dates and subtracting the number of holidays. Ex. =NETWORKDAYS(StartDate,EndDate) does that very nicely.


    My question is, how do I determine what workday number today is so I can figure out how many workdays are left in the month.


    I did come up with a formula that tells me how many calenadar days are left in the month:
    =(EOMONTH(TODAY(),0)-TODAY()) but it doesn't tell me how many workdays are left and I have been unable to adapt it.


    For example, today is Aug 29th. The first formula tells me there are 22 working days in Aug. while the 2nd formula tells me there are 2 days left in the month, but there is only one working day left in the month. I would like to accomplish this using only one cell as input, which is a hardcoded "Period ending date" which is typically yesterday. Thank you.

    Re: Formula to calculate number of working days left in month


    With =today() in A1(or replace the A1 with today() in the formula) try in another cell, this ARRAY formula.


    =EOMONTH(A1,0)-A1+1-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&EOMONTH(A1,0)-A1+1)))=1,1,0))

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

    Re: Formula to calculate number of working days left in month


    Thank you for looking at this. I tried your formula as is, and with today() inserted as you suggested:


    =EOMONTH(TODAY(),0)-TODAY()+1-SUM(IF(WEEKDAY(TODAY()-1+ROW(INDIRECT("1:"&EOMONTH(TODAY(),0)-TODAY()+1)))=1,1,0))


    and both give a result of 3 but the result should be 1, assuming your formula is calculating the number of working days left in the month(?)

    Re: Formula to calculate number of working days left in month


    =EOMONTH(TODAY(),0)-TODAY()+IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0))


    This takes care of the last two days issue, subtracting 2 days from working days if the last day falls on a sunday, and 1 if it falls on a saturday, like this month.
    You could probably add the last section
    +IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0)) to what was listed before and it should correct the ending days issue.


    using 0 for end of month means weekdays fall from 0-4, so 5 is saturday and 6 is sunday. All my ending does is delete 2 days if it's a sunday, and 1 if it's a saturday, but that fixes your issue with the earlier formula...

    Re: Formula to calculate number of working days left in month


    I set the date on my PC ahead to Nov 15 and received the following results using the suggested formulas:


    =EOMONTH(TODAY(),0)-TODAY()+IF(WEEKDAY(EOMONTH(TODAY(),0),3)>5,-2,IF(WEEKDAY(EOMONTH(TODAY(),0),3)>4,-1,0)) = 14


    =EOMONTH(TODAY(),0)-TODAY()+IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0)) = 15


    =EOMONTH(TODAY(),0)-TODAY()+1-SUM(IF(WEEKDAY(TODAY()-1+ROW(INDIRECT("1:"&EOMONTH(TODAY(),0)-TODAY()+1)))=1,1,0))=14


    =EOMONTH(TODAY(),0)-TODAY()+1-SUM(IF(WEEKDAY(TODAY()-1+ROW(INDIRECT("1:"&EOMONTH(TODAY(),0)-TODAY()+1)))=1,1,0))+IF(WEEKDAY(EOMONTH(A1,0),3)>5,-2,IF(WEEKDAY(EOMONTH(A1,0),3)>4,-1,0))=14


    None of them are doing what I want. On Nov 15th there will be 9 actual business days left in the month. (10 business days minus Thanksgiving) These formulas appear to be giving me the number of calendar days left in the month which is not what I need. Similarly, when I set my PC to Sep1, I get results of 29 and 30 but with few exceptions there are never more than 22 business days in a month.

    Re: Formula to calculate number of working days left in month


    I think I solved it. =NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0)) give the number of working days left in the month based on whatever today is. It doesn' take into account holidays however so if anyone could suggest how that could be done I would be very much in your debt! Thanks to everyone who replied.

    Re: Formula to calculate number of working days left in month


    Sorry for not getting back to you sooner. Long working weekend. Glad you found a solution that works. As for the holidays, maybe setting all holidays in a list with their actual dates and setting up a range that, when that date falls within the month, it subtracts one day? Just a thought. Will try to work something out in excel in a bit... Really need 26 hours in a day.

Participate now!

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

Register Yourself Login