Paul Grenier, Lead Moderator
AdamCarr
Member
I need to work out the total weekdays used for a period of time (say 13 days) this has proven to be more difficult than I first anticipated.
I have a start date, end date and a total days column (the total days column is just end date - start date) to give the total days used.
Any ideas on how I could achieve this?
Looks like Excel has a function you can use, called "NETWORKDAYS", but unfortunately, this one doesn't work in SharePoint.
AdamCarr
Member
Yeah, I was quite disappointed by the fact it didn't have that function in SharePoint. Also DateDif doesn't seem to have a Week function as well, which would make all of this a lot easier.
After a lot of googling I've managed to locate a formula from Oscar Medina's Blog, which I have modified ever so slightly but seems to do the job nicely.
You will need a 3 Columns
Start Date - Date/Time
Return Date - Date/Time
Working Days - Calculated (Single Line Text)
Using this:
=(DATEDIF([Start Date],[Return Date],"d"))-INT(DATEDIF([Start Date],[Return Date],"d")/7)*2-IF((WEEKDAY([Return Date])-WEEKDAY([Start Date]))<0,2,0)
Credit to Oscar for sharing.
That formula makes my brain hurt. ;-)
There might be something wrong with the formula. I tried it out. Usually it's correct, but when StartDate = 1/14/2009, and ReturnDate = 1/31/2009, then the formula says the result is 13, but it should really be 12, shouldn't it?
Laura Rogers
formulate is correct
DATEDIF calculates the difference of 2 days, just like 5-3=2 (not 3)
if you need to include today in the total, just add a +1 on the end.
I wrote about a similar question on the SharePointU.com forums about a year ago where I talked about how to create a calculation that acts similarly to the "Networkdays" function in Excel (see post here: http://www.sharepointu.com/forums/p/2429/6725.aspx#6725).
The formula is:
=IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))
It calculates the difference between two given dates ("start date" and "end date") excluding weekends.
It's been awhile since I've worked with it, but from what I remember, it calculates a day as a 24 hour period (starting just after midnight through the following midnight), so a "Start Date" and "End Date" of 1/14/09 would return a value of 1 since it's counted as one full day (your formula would return zero).
It uses the "WEEKDAY" function to validate that the date to be counted is Monday" through "Friday" (like yours), but counts the starting day as well as the ending day as full days (due to the 24 hour mention above).
This formula is a bit more complicated, but it does seem to do the trick (although as Igor mentioned, you can modify your formula to include the starting date in the count as well).
- Dessie
AdamCarr
Member
I think I have cheated in a way, I have asked users to enter their holiday start and then the return day, the day they will be back to work on.
I've done a calendar view of the holiday request list and it works out the days correctly, using my formula. This could be down to the way I am doing things with the end date being the actual day of return to work.
For example if you want the first two weeks of February off you would book
02/02/09 (Holiday Begins) - 02/16/09 (You will back at work this day, and I'm not used to using US dates...).
Because SharePoint defaults the a Date/Time Only field to use a Time Value of 00:00 it only creates a date span range of 1 day. Looking at the Calendar List - an All Day appointment that lasts all day ranges from 01/30/2009 00:00 to End Time 01/30/2009 23:59. Seemed an easier fix than making people enter the time values for their holiday so it shows up in the calendar view correctly.
Dessie's formula works correctly with the Time's correctly configured, as per all day events in the calendar lists.
Thanks for the all responses.
You must log in to post.
