06-01-2016 10:50 PM - edited 06-02-2016 01:36 AM
If I want to calculate difference between two dates excluding weekend(Saturday, Sunday). How to achieve this? Suggest some help.
06-02-2016 07:08 AM
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
06-02-2016 09:40 AM - edited 06-02-2016 09:42 AM
Here is the screenshot and formulas for each line below, frankly it should be possible to simplify but I could not see how...
DateDiff(DateValue(Date1.Text, "it" ), DateValue(Date2.Text, "it" ))
Mod(DateDiff(Date(2000,1,3), DateValue(Date1.Text, "it" )),7)+1
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))))
Basically it is whole weeks*5 plus an adjustment for the extra days depending on how many week-end days they include