cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mrunal
Level: Powered On

Date Difference excluding Weekends

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

hngdev
Level 8

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

Steelman70
Level 10

Re: Date Difference excluding Weekends

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

swetha
Level: Powered On

Re: Date Difference excluding Weekends

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