Date in Excel if it is a Working Day

    I was looking for a method that mentions the date if it is a working day

    I have a list of 12 Dates as below

    20-01-23

    23-01-23

    24-01-23

    25-01-23

    27-01-23

    30-01-23

    31-01-23

    01-02-23

    02-02-23

    03-02-23

    06-02-23

    07-02-23


    Please note that I have not mentioned Saturday and Sunday as it is a Holiday

    I have not mentioned 26-01-23 as it is holiday here in India


    Now starting from last date mentioned ie. 07-02-23 I wanted to create a formula that mentions 1 day earlier than today, For Eg. today is 08-02-23, and 1 day early it should mention 07-02-23, if it is a working day else the previous date should be mentioned. ( For. Eg. If Previous Day is Sunday it should Take Friday as Saturday is a holiday )


    Then go to 06-02-23 that is 1 day early from 07-02-23


    Now see 03-02-23, here 4th and 5th is Saturday and Sunday so it is not mentioned so it took date as 03-02-23.


    I need this as I have to change the date every day for getting the latest price for last 12 days.

    Hi,


    Say you have the Holiday date =DATE(2023,1,26) in cell E2 ...and .... in cell A13 your 'initial date' =DATE(2023,2,7)


    Then, you could have in cell A12 the following formula : =WORKDAY.INTL(A13,-1,1,$E$2)


    to be copied upwards till cell A2


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

    Quote from skamat

    WOW, thanks works very well

    Glad to hear it helps :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login