Excel formula to get dates excluding saturdays and sundays

[abhay_547]

Hi All,

I have a date column in excel against a product column where I have to populate different dates against each product based on the expected data arriving timeliness for each product. for e.g. for product1, i have expected timeliness of Today minus 2 which means if today is 3rd Sept then I will get the data of 1st Sept and same way for some products it's T-1 then it means on 3rd Sept I will get the data for 2nd sept and third condition is T .i.e. Today in which the data will be as of today for that product and the forth condition is weekly which we can ignore as the date for such product data is not fixed. Now I want to arrive at a formula which will give me T-2, T-1 dates first I straight away used the below formula

.i.e. =today()-2 for T-2 and =today()-1 for T-1

but the above formulas also give me the dates of that days which are sunday or saturday so I want to exclude saturdays and sundays and get only workdays .i.e. Monday to Friday dates. Please help.

Thanks a lot for your help in advance.

 

[VoG]

For T-2 try

=WORKDAY(TODAY(),-2)

In Excel 2003 and earlier this requires the Analysis Toolpak from Tools > Add-ins.

 

[abhay_547]

Hi VOG,

Thanks a lot for your quick reply, but I don't want to use the workday formula as we have excel 2003 at our end and though I check the analysis tool pack in the addin option but it's not necessary that others to whom I will be sending this file will have that option checked in in the addin already and as those people to whom the file is sent are most senior level guys I can't ask them to follow any such instructions to view my file contents. Hope you understand. I would like to use a formula which will work in any person's pc irrespective whatever excel version they use. Please help.

Thanks a lot for your help in advance.

 

[MikeWx]

One alternative for T-2 is:

=TODAY()-CHOOSE(WEEKDAY(TODAY()),3,4,4,2,2,2,2)

T-1 would be

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,1,1,1,1,1)

It shows how WEEKDAY made the process easier.

Mike