+ Reply to Thread
Results 1 to 8 of 8

Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

  1. #1
    Registered User
    Join Date
    10-12-2012
    Location
    AU
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1

    Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    The title pretty much explains it. A spreadsheet I use daily requires me to put a despatch date. The despatch date is always the next working day, for example - if it's monday, despatch date will be tuesday. The problem I have is that the next working day after Friday is monday, so how can I check if it's a friday and then insert monday's date?

    This is what I'm currently using for just tomorrows date - Having to manually put in the date each friday:
    ActiveCell.FormulaR1C1 = DateSerial(Year(Now()), Month(Now()), Day(Now()) + 1)

    Cheers

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    Hi Cboeree,

    Welcome to the Forum.

    Looks like you may have some Code in your Workbook.

    Could you please upload a sample of your WorkBook for us to have a look at?
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    Try it like this:

    Please Login or Register  to view this content.
    although I'm not sure why you couldn't just do:

    Please Login or Register  to view this content.
    for the bottom line.

    Hope this helps.

    Pete

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    Try this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or this Function in VBa
    Please Login or Register  to view this content.
    Last edited by Marcol; 10-12-2012 at 04:41 AM. Reason: Added VBa alternative
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    @ Pete_UK,

    Not bad at all!

    But don't you think Marcol gave us a brilliant little Formula? I would definately use that instead of VBA.

    Thank you for helping out here guys!

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    Hi Cboeree,

    And so we all seem to have forgotten about Annual Holidays, Public Holidays, etc. where none of the above solutions would be of much help.

    The attached WorkBook takes care of it all, where valid working days are recorded in Sheet2, to reflect the correct delivery date on Sheet1.

    Also see the "Please consider" note at the bottom of this post.

    No offence to Marcol and Pete_UK
    Attached Files Attached Files

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    Formula-wise you could use WORKDAY function

    =WORKDAY(A1,1,holidays)

    WORKDAY is a built-in function in Excel 2007 - in earlier versions you need Analysis ToolPak add-in
    Audere est facere

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Insert tomorrow's date unless Friday. If Friday, insert Monday's date.

    @ daddylonglegs,

    Formula-wise, I agree with your =WORKDAY(A1,1,holidays), but I am not sure how comfortable the OP is with Named Ranges or the application thereof.

    If he is o.k. with that he can easily do the replacements.

    To be honest, I cannot remember when last I have used Excel 2003, and forgot about the limitations of earlier versions. LOL

    Thank you for you valuable input.

    @ Cboeree,

    And so we all learn. I am attaching an Excel 2007 WorkBook, showing you both options, for your consideration.
    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)

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