Excel date formula modification needed to ignore Saturday and sunday

[Anand307]

Looking for a date formula which performs the below – as of now I have =TODAY()-1, but this formula is does not consider weekends as holiday. So on Monday excel formula will be showing the date for Sunday which is incorrect, instead it should be reflecting last Fridays date. Please help, and it is recurring and will be used throughout the year.

 

[Khalid NGO]

Hi Anand,

Use WORKDAY function like:

=WORKDAY(TODAY(),1)

If you want to exclude holidays also, make a list somewhere for example in the range B2:B10, and add it to the third parameter as:

=WORKDAY(TODAY(),1,B2:B10)

Regards,

 

[Anand307]

Thank you Khalid.. All the saturdays and sundays are holiday

 

[bosco_yip]

1] Instead of formula =TODAY()-1, try,

=TODAY()-MID(1231111,MOD(TODAY(),7)+1,1)

2] Or,

=A1-MID(1231111,MOD(A1,7)+1,1)

Where A1 is a date

Regards
Bosco

 

[Anand307]

Thank you Bosco