+ Reply to Thread
Results 1 to 8 of 8

How do I create work day only calendar dates?

  1. #1
    Registered User
    Join Date
    11-26-2004
    MS-Off Ver
    2010
    Posts
    25

    Question How do I create work day only calendar dates?

    Attached is my little project that has been I have been working on for days. Ironically, what I need to do is easy.... But I also want dates that auto update correctly, but only for workdays, no sats or suns.

    The dates I'm concerned about are in the B column. (Heck provided I get this figured out, I wouldn't even need the A column, and the sheet would be all the better for it)

    So for example, say Sunday is the 1st.... I want the first entry (Monday of course) to read 2nd.

    Can anyone help me please?

    Thank you for saving my sanity once again.

    Almost forgot! This if for Excel 2003

    Edit: Oh, just noticed, while playing with this before, I added A1 as Year, and B1 as Month ... then used those for reference cells... I did some other stuff and confused everything, so never saved. I reuploaded the file to reflect that change
    Attached Files Attached Files
    Last edited by rjmckay; 05-23-2011 at 01:03 PM. Reason: added a comment

  2. #2
    Registered User
    Join Date
    11-26-2004
    MS-Off Ver
    2010
    Posts
    25

    Re: How do I create work day only calendar dates?

    bump, is it possible?

  3. #3
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How do I create work day only calendar dates?

    Hi

    Provided you have the Analysis Toolpack loaded, then this may suffice for your needs.

    You can add Holidays in to the Workday formula as well if you wish
    =WORKDAY(B4,1,holidays)
    where holidays is the range of cells holing your holiday dates.
    Attached Files Attached Files
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  4. #4
    Registered User
    Join Date
    11-26-2004
    MS-Off Ver
    2010
    Posts
    25

    Re: How do I create work day only calendar dates?

    Quote Originally Posted by Roger Govier View Post
    Hi

    Provided you have the Analysis Toolpack loaded, then this may suffice for your needs.

    You can add Holidays in to the Workday formula as well if you wish
    =WORKDAY(B4,1,holidays)
    where holidays is the range of cells holing your holiday dates.
    Thank you. I am using Analysis Toolpack .

    I did look at that holiday option, but not sure how that would help me. For one, I guess I'd have to put every single weekend as a holiday. I while work intensive, I can see that working for one year.... but would fall apart the next year.

    Unless I'm not seeing or not understanding something?

    Thank you!

  5. #5
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How do I create work day only calendar dates?

    Hi

    You don't have to worry about weekends, WORKDAYS takes care of that.

    See attached file where I have a sheet called holidays, where I have listed Public Holidays in the UK, and created a dynamic range called Holidays, so if you add more they will automatically get included.

    Take a look at August (and the new sheet I created for April - you just copy the sheet and change the value in cell C2) and you will see that they miss out the public holidays in those months.

    Yes, each year, you will have to amend the dates in Holidays

  6. #6
    Registered User
    Join Date
    11-26-2004
    MS-Off Ver
    2010
    Posts
    25

    Re: How do I create work day only calendar dates?

    Quote Originally Posted by Roger Govier View Post
    See attached file where I have a sheet called holidays
    Oh my goodness.... when I seen your previous posts, I assumed that the attached file was still my own file. Yours even looks better than mine! LOL

    Thank you so much.

    I do have one question in mind.... what if the 1st day of the month is on a Saturday or Sunday? Edit: October, 2011 is a good example of this. Is there a way to automatically account for that.... or do I just have to manually handle that on a case-by-case basis?

    Thanks again for your help!

    Rj
    Last edited by rjmckay; 05-23-2011 at 12:44 PM. Reason: added info

  7. #7
    Registered User
    Join Date
    11-26-2004
    MS-Off Ver
    2010
    Posts
    25

    Re: How do I create work day only calendar dates?

    never mind! The answer to that question was too easy =IF(A2="","",IF(WORKDAY(A2,1)>EOMONTH(A2,0),"",WORKDAY(A2,1))). Thank you very much for all your help!

  8. #8
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How do I create work day only calendar dates?

    Hi

    Change the formula in cell B4 of each sheet to
    =IF(WEEKDAY(A2,2)>5,WORKDAY(A2,1),A2)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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