+ Reply to Thread
Results 1 to 3 of 3

Display a previous date without weekend and holidays

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    65

    Display a previous date without weekend and holidays

    Hi,

    I am working in reconciliation and I always work 1 day behind. I need to display the previous date in my work. Usually I will input:

    =TODAY()-1

    But if it is Monday, it will display Sunday.

    1. Is there any code that will display as Friday instead?

    2. If Monday is a holidays and we are Tuesday. I will have to display Friday too, so what code can we input for this one?

    Best Regards,

  2. #2
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Display a previous date without weekend and holidays

    =WORKDAY(today(),-1,holidays)
    Last edited by Teethless mama; 09-05-2013 at 09:47 PM.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Display a previous date without weekend and holidays

    Try this...

    =WORKDAY(TODAY(),-1)

    To exclude holiday dates...

    List the holiday dates in a range of cells like A2:A15. Then:

    =WORKDAY(TODAY(),-1,A2:A15)

    Format the cell as Date

    Note that the WORKDAY function requires the Analysis ToolPak
    add-in be installed if you're using a version of Excel prior to
    Excel 2007. If you enter the formula and get a #NAME?
    error look in Excel help for the WORKDAY function. It'll tell you
    how to fix the problem.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Elapse time based on work hour less weekend and holidays
    By russellj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-23-2013, 06:29 AM
  2. Change Weekend Date To The Previous Friday
    By MarkMcCann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2012, 09:45 AM
  3. [SOLVED] Max Weekend Value, display date value was seen
    By johnjacob in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-25-2012, 10:36 AM
  4. SOLVED - Display the date of the previous Monday?
    By tavlrb in forum Excel General
    Replies: 2
    Last Post: 02-26-2011, 01:50 AM
  5. display date, excluding weekends & holidays
    By emueller in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-10-2008, 03:55 PM

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