Comparing Dates in SharePoint Using XSL
Especially when you are using Data View Web Parts (DVWPs), but anytime you are using a Web Part that allows custom XLS to control display, you may want to do a test on dates. Doing this is relatively easy if you know a few of the ddwrt namespace date functions.
In this example, let’s say that you’d like to display calendar events that occur today or later. (Old calendar events aren’t of much use to display.)
The first step is to get today’s date. You can do this with the following XSL:
<xsl:value-of select="ddwrt:Today()">
(’02/19/2008′ if your language is set to 1033)
You can also get the full ISO date and time with:
<xsl:value-of select="ddwrt:TodayISO()">
(‘2008-02-19T11:15:02Z’)
For comparison purposes, though, you’d like to have the date in YYYMMDD format. You can do this conversion with the FormatDateTime ddwrt function:
<xsl:value-of select="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd')">
(‘20080219’)
So, to compare your event date to today’s date, it’s a quick test:
<xsl:if test="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd') <= ddwrt:FormatDateTime(string(@EventDate), 1033, 'yyyyMMdd')">
Hi Marc.
Is there a way to display the days between Today() and another column, EndDate for example?
I’ve tried to do as mentioned above but some of the results are not logic…
For example:
Today: 05/28/2012
End Date: 06/04/2012
Result: 7
Thanks,
Dolev.
Dolev:
Date arithmetic is always problematic, regardless what language you’re working in. You should take a look at the date arithmetic templates I link to in my post called Date Arithmetic in SharePoint DVWPs.
M.
I am currently looking for a solution to filter on a dataview to bring back birthdays for current and month and 2 weeks.
I currently use the following:
[substring-after(@ows_BirthMonthNumber,’#’)=number(ddwrt:FormatDateTime(string(ddwrt:Today()),1033,’MM’))]
This brings back for the entire month but i need something that will bring back for 2 weeks in that month.
Any ideas?
Regards
Troy
Troy:
Getting the current two weeks is probably going to be tricky. You’ll need to figure out the start and end date for the range and then filter for dates between them.
M.
Hi Troy,
Looks like you were able to show birthday for current month, I used your XPath expression but unable to work it through. I need to show birthday for current month. If you get this message please let me know.
My requirement is to have a calculated column based on the date range. I have given a name “CalcuDate”
in CalcuDate, i’m gonna store whenever the condition is met
for example:
-60 days and less than equal to 180 days will be displayed “GREEN” on the CalcuDate
-less than 60 days will be displayed “YELLOW” on the CalcuDate
-DateX that is equivalent to DateY will be displayed as “RED” on the CalcuDate
here is my code:
=IF(AND([Current Date tmp]=[Critical Date],[Action Taken]=”No”),”RED”,”YELLOW”), IF(AND(DATEDIF([Current Date tmp],[Critical Date],”d”,[Critical Date]<=180)))
please help!
DC:
Calculated columns are only evaluated when you save the item, so this type of ongoing calculation just doesn’t work. Usually people turn to script.
I’m working with someone on a cool new way to do this, though, so stay tuned to my blog.
M.
Thank you Very much!!.. nice information
Hi Marc,
Ive created a conditional format if a SharePoint date column (LiveExpiry <= Today).
The date visible in the LiveExpiry column is of format 15/04/2015 (day,month,year) UK
However the conditonal format result is wrong, as it appears to reverse the day and month order.
I've tried tons of options but cannot get this to work. Would you know if there is a bug using UK formats?
Raj:
It’s not a bug, simply a different way that dates are displayed. You can use the ddwrt:FormatDateTime function to convert your dates to comparable YYYYMMDD formats:
ddwrt:FormatDateTime(string(@LiveExpiry), 2057, ‘yyyyMMdd’))
M.
Marc,
I *think* in your examples at the end of the post you should be using FormatDateTime exclusively instead of FormatDate.
Hi there, When the date is empty or null it is showing . Is there any way to check if it is empty or null. I tried the srting function doesn’t work. How can I validate date?
Appreciate your help.
Thanks,
Khushi
Khushi:
All data comes back from the lists as text, so you can simple check to see if the value has a length:
M.
This doesn’t actually work, at least not in SP2010:
ddwrt:FormatDate(string(ddwrt:Today()), 1033, ‘yyyyMMdd’)
You need to use
ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, ‘yyyyMMdd’)
Obviously an old post, but I seem to have contradicted myself. In the text I said FormatDateTime, but in the code samples I used FormatDate. Fixed for evermore, so thanks for the correction!
M.
Mark, thank you very much for this post. It helped me a lot.