Date Calculation

[stefaniejo]

One of my customers made a funky request. When calculating date ranges on their report they want to calculate calendar days but count Saturday and Sunday as 1 day.

Example: My start date is Wed, 1 Jun and my end date is Tue, 7 Jun. Wed counts as day 0, Thu counts as day 1, Fri day 2, Sat & Sun as day 3, Mon as day 4, Tue as day 5.

I thought about using the WEEKDAY function to determine the day of the week the start and end dates occur and calculating the workdays and adding 1 if a weekend fell between the dates, but it is possible that some of my date ranges could cover multiple weeks.

How would I calculate this with a formula/VBA code? Any ideas?

 

[Mr. B]

To do custom accumulative counting of the number of days like this, you will have to write a custom user defined function that will iterate through all dates between the starting and ending dates, using the weekday funcntion to determine if the date you are on should be included in the count of the days.

The first question that came to my mind was what happens if the ending date falls on a Saturday? Is that Saturday counted? Those are the kind of things you can address in your function.