+ Reply to Thread
Results 1 to 16 of 16

Formula to populate work days

  1. #1
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Formula to populate work days

    See attached example below.

    I am trying to write a formula that will count workdays, however not in total for a given month, but by day.

    I.E. I am trying to count across a row - 1,2,3 and then skip weekends and holidays. Do I need to manually create a table of data with labels like "weekday", "weekend" and "holiday" for 2016 and then write a formula or is there a simpler formula I can write?

    I know there is a WORKDAY function in excel, but I don't think that's going to populate what I'm looking for as it will just tell me the total number of workdays for the timeframe.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to populate work days

    create a range of holidays somewhere in your workbook and name that range "WorkHolidays" (for example to test I put 07/09/15, 13/09/15 and 01/10/15 in A20-A22) then put the number 1 in cell D15 and in E15 put
    Please Login or Register  to view this content.
    and drag that across as far as you need.
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    Fabulous! That works. However, on weekends it is duplicating what it put for Friday. Any idea how I can get it to populate blank cells for weekends/holidays? Also, I'm trying to make this spreadsheet as idiot proof as possible so monthly the user will just select the month from the dropdown and everything will populate, however if the first day of the month falls on a weekend - the user will need to move the formula around, any ideas on populating a single formula that is applicable for all months of the year? Really appreciate the help!!

  4. #4
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Formula to populate work days

    Rather than the formula you have in D4.

    I would use =($D$3&"/1")+0 in D4 (This calculates the first day of the month in cell D3)
    then in =($D$3&"/1")+D6 in E4 and copy accross.

    Windy

  5. #5
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    Windy,

    Thank you for that suggestion. My only potential concern is: I'm creating this template based on a 31 day calendar month, so for month's that have less than 31 days (like September), I will need the last column to populate "blank". I suppose I could modify with an IF statement for that purpose.

    Thank you very much as that will save me from manually populating a date table every year.

  6. #6
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    Windy - question: when 2016 gets closer will the calendar functionality automatically update the formula? For example, right now if I select January it shows Jan-15. We operate on a fiscal year cycle so I'll need that to be Jan-16 when I'm updating the file in Dec-15. Thanks!

  7. #7
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to populate work days

    Quote Originally Posted by Jess0121 View Post
    Fabulous! That works. However, on weekends it is duplicating what it put for Friday. Any idea how I can get it to populate blank cells for weekends/holidays? Also, I'm trying to make this spreadsheet as idiot proof as possible so monthly the user will just select the month from the dropdown and everything will populate, however if the first day of the month falls on a weekend - the user will need to move the formula around, any ideas on populating a single formula that is applicable for all months of the year? Really appreciate the help!!
    Put that calculation in row 16 instead of 15 and then in E15 put
    Please Login or Register  to view this content.
    and drag across

  8. #8
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    PJ - that works for weekends, but not holidays (since there is one in September). Also, now I have two rows of data vs one - though I could likely combine the formulas. Thanks, it's getting there!!

  9. #9
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to populate work days

    Actually, its a lot easier than I originally posted and covers holidays, change the formula in E15 to
    Please Login or Register  to view this content.
    And then hide row 16 so nobody can see it.

  10. #10
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    Maybe it's something with my formatting but when the formula gets to 9/8/15 (business day 5), the result is still blank even though 5 is greater than blank. Perhaps it's because no number populates the proceeding day? Also, if I hide the row underneath then it gets more complicated when the first day of the month isn't a business day because the user will have to unhide the row, update BD1 and the subsequent formula across the row and then re-hide the row. I'll have to think on it some more, but I feel like it's getting more complicated, not less.

  11. #11
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to populate work days

    Can you post your workbook as it stands now?

  12. #12
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    Of course, now it appears to be working. See attached, you can see I've grouped the "hidden" line. The "1" is highlighted yellow as it will need to be manually updated depending on when the first business day of the month is. I have also added a calendar showing workdays, weekends and holidays to the second tab.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to populate work days

    You shouldnt need any of the stuff in the 2016 Calendar tab except the range A32:A43, everything else should be calculated.

  14. #14
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Formula to populate work days

    Change D8 to
    Please Login or Register  to view this content.
    That will give you, your start day. After that its all there, it calculates the lot itself.

  15. #15
    Registered User
    Join Date
    03-27-2012
    Location
    Tampa, FL
    MS-Off Ver
    Excel for M365
    Posts
    53

    Re: Formula to populate work days

    Quick question before I basically scrap the calendar page: we store these for historic purposes. If down the road I try to open let's say 2013 information, will all the dates automatically reset to the current year if I don't have hardcoded dates like I do currently (i.e. if I use Windy's fix for date population)?

    Otherwise, I believe my problem is fixed and I thank you all for your input!!

  16. #16
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Formula to populate work days

    Have changed the layout slightly.

    Have hidden rows 4,5 & 6

    Used conditional formatting to hide repeated numbers.

    As you now enter the date there should not be a year issue.

    Windy
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] EOMONTH formula calculation only work days
    By kettlecorn22 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-03-2014, 03:26 PM
  2. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  3. Help on formula for calculating days of work
    By Renildrah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2012, 02:47 PM
  4. Populate a formula to update leave days
    By Sunil Verma in forum Excel General
    Replies: 3
    Last Post: 01-10-2012, 12:10 PM
  5. Replies: 1
    Last Post: 09-04-2007, 07:42 AM
  6. Work Days Formula
    By Hany ElKady in forum Excel General
    Replies: 10
    Last Post: 07-23-2006, 08:35 AM
  7. Formula using work days
    By trouble with work days in forum Excel General
    Replies: 2
    Last Post: 01-17-2006, 12:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1