Subtract number of working days, from a date

[Rick__]

Hi all,

You've all been so helpful in the past, so I thought you might be able to help me out once again.

I'm trying to work out lead times based on a cut off date, I need to subtract the lead time (Column B) from the cut off (Column C) to calcualte the last order date (Column D), however I need to make sure the fourmula only counts work days (Mon - Fri).

Bascially "Last Order Date" = "Cut off" - Lead Time (not counting weekdays).


Thank you in advance.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Product
[/TD]
[TD]Lead Time (in days)
[/TD]
[TD]Cut off
[/TD]
[TD]Last Order Date
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Phone Case
[/TD]
[TD]8
[/TD]
[TD]18/12/2018
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Charger
[/TD]
[TD]5
[/TD]
[TD]18/12/2018
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[kvngrn]

Hi,

Try

=WORKDAY(C2,-B2)

 

[Rick__]

Thats worked perfectly thank you kvngrn

 

[Peter_SSs]

Note also that you can build any known holidays into this. For example:

Excel Workbook
	B	C	D	E	F
1	Lead	Cut Off	Last order		Holidays
2	3	16/03/2018	12/03/2018		1/01/2018
3					15/03/2018
Subtract Work Days