Taming the Elusive “Calculated Column” – Date and Time – Part 1
Overview
I wanted to start off the series on the “Date and Time” set of functions by examining a specific problem that many folks have posted questions about – that is, how do you calculate the difference (number of days) between two dates, while excluding weekends? (Iain on the STP forums asked this recently, which sparked my interest in writing this)
At first glance, this seems like an obvious candidate for the “DATEDIF” function since by definition, it “Calculates the number of days, months, or years between two dates”. The problem with this approach however, is that by itself, it will count all days, including those specific ones we don’t want (weekends).
If, on the other hand, we were just working in Excel, we could take advantage of “NETWORKDAYS” since it will return the number of ‘whole’ workdays between two dates, but alas, we don’t have that one available within SharePoint*
*One of my most wanted features of SharePoint version-next (or next): make list functionality exactly the same as Excel spreadsheets. If you’re going to bother to model them after spreadsheets by including functions and abilities to perform calculations, you might as well go all the way and transform them into real working sheets. This to me screams “Office Live”!! We already have the ability to export to a spreadsheet with a data connection, so how about extending this a bit further by adding in the ability of exporting a spreadsheet built in Excel to a new list? Imagine creating the functionality you want within Excel, using all the power that comes with it, then choosing “Export to new SharePoint List” where it asks you to enter in the site address (or choose from a list of available connections), the name of the list, then “Export”…and done. You’d now have a new list based on your spreadsheet with the benefit of already having an active data connection setup so you can administer the list either through the list itself, or through the original spreadsheet. Any new columns, calculations (using the full-range of what’s available in Excel), or updates would be saved to both – in real time. Imagine the possibilities…
Since we can’t perform the “exact” same calculations we could otherwise do in Excel, we need to recreate the functionality performed in such a manner that can be compatible with SharePoint.
Let’s take a look at the formula that will accomplish this for us then examine it to see how its logic performs the date calculations.
The Formula
=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)))
On the surface, this does appear a bit daunting, given the amount of parentheses and functions, so let’s break it down into its core functions and examine what each does.
We have 12 functions:
1 “IF”
1 “AND”
4 ”WEEKDAY”
4 “DATEDIF”
2 “FLOOR”
We’ve already covered the “IF” and “AND” functions in detail during the series on “Logic”, so let’s look at the other three.
The Functions
WEEKDAY: returns the day of the week corresponding to a date.
Using the optional "Return_type" value in addition, dictates what day of the week to start with and it’s associated number (weeks will start on either Sunday or Monday):
1 (or omitted) = Sunday through Saturday (Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7)
2 = Monday through Sunday (Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7)
3 = Monday through Sunday (Mon=0, Tue=1, Wed=2, Thu=3, Fri=4, Sat=5, Sun=6)
The “Return_type” attribute is used to pick what day of the week is your starting day (either Sunday or Monday) and what numbering scheme you’d like to use to count by (7 numbers total – you can either start with a 1 and count to 7, or start with a 0 and count to 6 – either way, it still amounts to 7 numbers total).
The syntax of the function is:
=WEEKDAY(Date,Return_type)
Using the example of a “Date/Time” column with a date of “5/19/2008”, we’d have a value displayed of “2” because that particular day was a “Tuesday”. We could use any of the date formats for this, so the actual “Date/Time” column doesn’t necessarily need to be a “Date/Time” type, it could be just a regular text box with a date test in it, or some calculation resulting in the serial number for the date (“39952” is the serial number for that specific date and would return the exact same result).


DATEDIF: calculates the number of days, months or years between two dates.
The type of information returned is indicated by the "Unit" specified.
Syntax: DATEDIF(StartDate,EndDate,Unit)
Unit:
"Y" = number of years
"M" = number of months
"D" = number of days
"MD" = number of days ignoring the months and years
"YM" = number of months ignoring days and years
"YD" = number of days ignoring years
Using two dates (“Date/Time” columns, or any value represented in a date format), we can count the number of days between them:
Date1 = 5/11/2009
Date2 = 5/15/2009
Formula:
=DATEDIF(Date1,Date2,"D")
Result displayed = 4 (we’re using the “D” Unit to count days only)
This is accurate because there are 4 days difference between the two and SharePoint considers the end of a day to be at 11:59 p.m. (Date1 is a Monday, Date2 is a Friday: start counting on Tuesday: Tue, Wed, Thu, Fri = 4 days total).

FLOOR (part of the “Math and Trigonometry” set of functions, but will be used in our formula): rounds number down towards zero to the nearest multiple of significance ("significance" being the amount you want to round down to. Examples: using "1" would round down the number to the nearest whole number, ".1" would round down to the nearest 10th, "-5" would round down to the nearest negative five, etc.).
Syntax:
FLOOR(number,significance)
Using a “Number” column (could be an actual “Number” type, or a text value since SharePoint will automatically convert a string value representing a number into an actual number), we can see the simple “rounding” down performed:
Number = 15.1
Formula:
=FLOOR(Number,1)
Result displayed = 15 (since 15 is the nearest “1” number down)

Next time, we’ll continue on by “attempting” to decipher the formula.
Till then…
- Dessie
Dessie Lunsford
Points-of-Sharing
- Taming the Elusive "Calculated Column"
- EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Task List
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part II)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part III)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part IV)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Text and Data
- EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part I)
- EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part II-I)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-II)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-III)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part IV)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part V)
- EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part VI)
- Taming the Elusive “Calculated Column” – Lookup and Reference
- Taming the Elusive “Calculated Column” - Logic
- Taming the Elusive “Calculated Column” - Logic - Part 1
- Taming the Elusive “Calculated Column” - Logic - Part 2
- Taming the Elusive “Calculated Column” - Logic - Part 3
- Taming the Elusive “Calculated Column” - Logic - Part 4
- Taming the Elusive “Calculated Column” - Logic - Part 5
- Taming the Elusive “Calculated Column” - Logic - Part 7
- Taming the Elusive “Calculated Column” - Logic - Part 6
- Taming the Elusive “Calculated Column” - Logic - Part 8
- Taming the Elusive “Calculated Column” - Logic - Part 9
- Taming the Elusive “Calculated Column” - Logic - Part 10
- Taming the Elusive “Calculated Column” - Date and Time - Part 1
- Taming the Elusive “Calculated Column” - Date and Time - Part 2
- Taming the Elusive “Calculated Column” - Date and Time - Part 3
- Taming the Elusive “Calculated Column” - Date and Time - Part 4
- Taming the Elusive “Calculated Column” - Referencing Multiple Lines of Text Column
- Taming the Elusive “Calculated Column” – Date and Time – Part 5
- Taming the Elusive “Calculated Column” – Date and Time – Part 6
- Taming the Elusive “Calculated Column” – Date and Time – Part 7
- Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 1
- Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 2
- Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 3
Hi Dessie
Thanks for putting this together and i know you are not finished, but thought i would ad in your new formula
=IF(AND((WEEKDAY([End Time],2))1),(((TimeDIF([Start Time],[End Time],”D”)+1))-(FLOOR((TimeDIF([Start Time],[End Time],”D”)+1)/7,1)*2)-2),(((TimeDIF([Start Time],[End Time],”D”)+1))-(FLOOR((TimeDIF([Start Time],[End Time],”D”)+1)/7,1)*2)))
I changed Start Date to Start Time etc as I am using the Calendar field.
I get the following error:
The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
at Microsoft.SharePoint.Library.SPRequest.UpdateField(String bstrUrl, String bstrListName, String bstrXML)
Iain,
Check your syntax – you have “TimeDIF” instead of “DATEDIF” (looks like you performed a “Find and Replace” and chose to “change all”).
- Dessie
Hi Dessie,
I’m trying to create a calculated column showing som text based on the version on the document in a document library. What I want is to show the text Not published if it’s below 1.0, Published if it’s 1.0, and Working if above 1.0.
Everytime I try to use the column Version in the formulat field in any way, I get an error. Do you have any tips on how to accomplish this task?
Thanks,
Frank
Hi Dessie,
Thank you for writing this article. It has been very helpful.
I’m trying to expand on your formula since I am using time stamps to determine the difference. For example:
Start Time- 7/6/09 10:00 AM (Mon)
End Time- 7/8/09 9:45 AM (Wed)
To determine the difference of the days, I use the formula INT(End Time-Start Time). I substituted this formula for DATEDIF.
By using the current formula, I get 2 days, which for my purposes is incorrect. It would still be only one day since I have not hit 10am again on the second day. Examining the formula, I determined that the +1 at the end of the DATEDIF [now, with the substitution, is INT(End Time-Start Time)+1] was the problem, so I eliminated it.
From that point, the formula only works during that week, and the “if” conditions don’t become TRUE/TRUE (as far as I could tell).
If you use a day later in the week, such as 7/8/09 (Wed), it will work through the weekend, but when the date become 7/14/09 (Tues), the formula breaks.
Any insight how I can get this formula to work with the time stamps?
Hi Dessie –
I was able to use this function, but unfortunately after 24 hrs I have to do some kind of refresh otherwise the field shows an error. Have you heard of this issue with calculated fields?
Hi Dessie
I have this working like a dream at the moment – but now have broken it.
I also want to track half days, so have a half day choice where someone can either take the morning or afternoon off.
How can I build this in so that the correct number of days show up.
Iain
Dessie, Thanks for this series of articles. I’ve learned a lot by reading through them. I’m proud to say that I’m no longer afraid of the calculated column. I don’t even have any ‘real’ Excel experience and I can still find my way with the help of your articles.
is there any formula to get just the time part from date time in 12 hour format. I have used the following formula and getting time in 24 hour format.
TEXT([My Date],”h:mm”)
any help on this is greatly appreciated.
shwetha,
Times in SharePoint are stored in a serial number format that equates to a 24 hour format when viewed through calculations.
There may be a few easier ways of doing what you need, but this formula will check to see what the value of the “HOUR” is and convert it appropriately depending on whether or not its less than, equal to, or greater than “12″:
Hopefully this will help,
- Dessie
I am trying to work around Sharepoint’s limitation on use of ‘TODAY’ in calculation formulas and an apparent inability to deal with dynamic dates. I need to calculate the number of days left from the present (each day, i.e. the dynamic part) to a specific and static date (due date). Any clues on how to achieve this?
Thanks.
Hi Is there anyway to use the formula with a tweak to allow for .5 days to also be accounted for? I am using this as a holiday calender and want to rule out weekends but calculate 0.5 days using the time of holiday part
I guess my question is not that deep I have a simple formula in a issue list.
=[Date Opened]-[Date closed]
However ,if there is not a date close and the task is still active then the active view will show A large about of total days. What I’m looking to do is have it where if the date close is left open the user will see a blank or a ” open ticket “. and if there is a date than it will follow through with the calulation.
This formula is wicked awesome! I need just one small tweak. I have a ‘Date Received’ and a ‘Date Completed’ column. If the dates are the same, the above formula returns 1. Is there a way to calculate the difference and return a zero (0) when these two dates are the same?