If I want to calculate difference between two dates excluding weekend(Saturday, Sunday). How to achieve this? Suggest some help.
Thanks for the post.It's working great!!!!!
Here is the screenshot and formulas for each line below, frankly it should be possible to simplify but I could not see how...
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
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
WarrenBelz
146,743
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,079
Most Valuable Professional