Reply
Highlighted
Level: Powered On
Posts: 18
Registered: ‎06-01-2016

Date Difference excluding Weekends

[ Edited ]

If I want to calculate difference between two dates excluding weekend(Saturday, Sunday). How to achieve this? Suggest some help.

Level 8
Posts: 93
Registered: ‎05-13-2016

Re: Date Difference excluding Weekends

I think you can you use DateDiff function to do so:

- Use DateDiff with day unit > get days

- Use days to get weeks

- Multiply weeks by 2 for weekends

- Get days minus weekends

Hope it helps

Level 10
Posts: 256
Registered: ‎05-20-2016

Re: Date Difference excluding Weekends

[ Edited ]

Here is the screenshot and formulas for each line below, frankly it should be possible to simplify but I could not see how...

 

 

 

CaptureWeekDays.PNG

 

Date(2016,6,15)

 

Date(2016,6,27)

 

DateDiff(DateValue(Date1.Text, "it" ), DateValue(Date2.Text, "it" ))

 

Mod(CalDays.Text,7)

 

Mod(DateDiff(Date(2000,1,3), DateValue(Date1.Text, "it" )),7)+1

 

Weekday1.Text+CalDaysMod7.Text

 

If(CalDaysMod7.Text="0",0, If(Value(Weekday2x.Text) < 7, CalDaysMod7.Text, If(Weekday2x.Text="7", Max(CalDaysMod7.Text-1,0), CalDaysMod7.Text-Min(2,8-Weekday1.Text))))

 

RoundDown(CalDays/7,0)*5+ExtraDays

Basically it is whole weeks*5 plus an adjustment for the extra days depending on how many week-end days they include

Level: Powered On
Posts: 16
Registered: ‎11-03-2016

Re: Date Difference excluding Weekends

Thanks for the post.It's working great!!!!!