SharePoint TODAY field and calculated fields…


A customer sent me a link to a page they found about using the Today field in calculated sharepoint fields.  I don’t know how you would fear with our support people … but here is the note from Pete Blair on the workaround:


It is possible to use “Today” in a function as a reference to today’s date
(despite what SharePoint tells you).  There is a very simple work around that
doesn’t involve very much effort or complexity.

First, create a new column in your list with the Column Name of “Today”.  
Click “OK”.  (It doesn’t matter what type of column or data it is, this is
just a place holder).

Next, create a column with the data where you would like to use “Today” as a
reference to today’s date.  The column type should be “Calculated”.  In the
formula field, create your formula using “Today” as if it held the current
date/time.

SharePoint will calculate the formula based on the assumption that you will
be using “Today” as a reference to the new column you just created.

Next, edit the new column named “Today” that you created in the first step.  
On the very bottom of the page, click “Delete”, to delete the column.  In
your formula, SharePoint will keep the reference to “Today” but it will
change from referencing your column, to a reference to the current date.

This can be used in any of the Date and Time functions, but I haven’t tested
it beyond that.

Notes:
If you want to edit the formula containing “Today” SharePoint will not let
you, and will give you the same error about not using Today or Me in a
function.  To get passed this problem you have to temporarily create another
Today column.

The same functionality will also work with Me.

Comments (64)

  1. Hallo zusammen,

    oft wurde diese Frage schon gestellt, bis jetzt wußte leider keiner eine Antwort. Chris…

  2. rachel says:

    I have been using this trick for quite a while and it has been really useful.  I first spotted this on Mark Kruger’s blog http://www.sharepointblogs.com/mkruger/archive/2005/03/18/1500.aspx.

    The drawback is "the formula will not calculate all list items on refresh but rather by the addition and deletion of a new Today column or by editing each of the list items individually.  However, you can code this to make it more dynamic…"

  3. Neil says:

    Nice workaround – I am hoping that filter formulas are seriously addressed in v3.  But, in the mean time, this trick got me a gold star from the office manager when I fixed her admin subsite.  And we all know that office managers secretly run the world.

    Neil Bailey

    training specialist / web monkey / it wonderboy

  4. Marc says:

    This is all very well and may (or may not) work correctly – however, this is completely useless from a support point of view as the next person to edit the list wont know how [Today] has been faked so may inadvertently break this formula or try and replicate it (not knowing about this trick).

  5. KRAYSCO says:

    This workaround works fine for new lists however I have a list already containing hundres of list items. The following solution works much better for me:

    Create a new calculated field (FilterDate) and use the original Date field as the input source and then add the formula. In my case,  I used "= [Document Date] + 31" as the formula. Set the output type to date. Then I set my filter up so that FilterDate >= [Today]

    Enjoy!

  6. keren sar-el says:

    Hi,

    it is a great tip. for some reason  – when i’m using it now in sharepoint V3 – it seems that the calculated column which is based on Today doesn’t show the righ value (it seems the calculation use the value of the day in which i created the column or the item as Today, and is not changed dynamically…)

    is it so?

    v-kesar@microsoft.com

  7. Sam says:

    @keren sar-el: as rachel said higher up in these comments:

    "The drawback is "the formula will not calculate all list items on refresh but rather by the addition and deletion of a new Today column or by editing each of the list items individually.  However, you can code this to make it more dynamic…"

    March 16, 2006 12:09 PM "

  8. Gold8899 says:

    Now that we all know all the way to the point the calculated field does not refesh automatically, and "needed to code it to make it moredynamically…"  now, it stops here…  Any solution to make it more dynamic????  I am sure a lot of people would like to find out!

  9. Martin says:

    Hi there,

    I also can’t find any explanation anywhere of the coding required to make refreshing more dynamic…

    I’m beginning to think that there is some inside joke about something really obvious 🙂 Please, please tell us how?

  10. Brenden says:

    Hi, Can someone post the workaround again? This blog site has been shut down!

    Many thanx,

    Brenden

  11. T. says:

    I seriously can’t believe Microsoft created a product that does not allow calculations on today.  Are they smoking crack?

  12. Ed says:

    This trick no longer works. I just tested it with the most recently install of wss v3.

  13. J. Mills says:

    It’s my understanding that the drug of choice in the Seattle area is heroin, not crack.  😛

  14. Tim says:

    This little piece of code worked fine for calculating a running today of days…

    =IF([Completion Date]-[Start Date]<0,"0",IF([Completion Date]-[Start Date]=0,"1",[Completion Date]-[Start Date]))

  15. Maria says:

    Any updates about making the calculated field to refresh automatically?

    Thanks a lot for the advice.

    Regards,

    Maria

  16. gina says:

    I second Maria’s request.  I’m displaying a different image depending on the date difference from TODAY.  The logic works fine, but it only evaluates if I open a list item and save it (I don’t make any edits).

    It seems that the calculation is not applied when you open the list but only when each record is "edited".

    Any help would be appreciated!

  17. Adam says:

    I tried the above fix, the one submitted by chris johnson and it worked fine for me. The dates dont remain static, i added it last friday when the date was the 24/08/2007, and i’ve checked it today 28/08/2007 and its actually changed the date.

    Great help, thank you

  18. Sharepoint Services 2003 – Workaround for filtering on today’s date

  19. Sharepoint Services 2003 – Workaround for filtering on today’s date

  20. SergIO says:

    It didn’t work for me. I’ve tested it with sharepoint v3 and it doesn’t work. The date remains static… Any other idea?

  21. daveclark says:

    it worked for me, but I’m getting negative days for recent posting, ie today-created = -ve.  I guess this is because today is defaulting to 12:00 or 00:00.  I’ll check tomorrow, when this should filter out.

  22. Softplay says:

    No scary experiences using this workaround. When MS does not want us to use Today/Me in calculated column, there should be a reason for it??

  23. Samuman says:

    I have used the above method to get todays date fine, but what if I want to display date and time not just the date (much like the MySQL function NOW())? ‘Today’ function seems to only get the date. I am using the formula =IF([is completed?], TODAY) with the column returning a date AND time in the format dd-mm-yyyy 0:00 where dd-mm-yyyy is today’s date but 0:00 is not the time the field was calculated. I want 0:00 to reflect the time the field was calculated. Any help? Seems like Microsoft makes this process stupidly difficult!

  24. Bill C says:

    I have tried this workaround and it appears to work.  However when you export to EXCEL 2003, the field with the reference to "Today" displays "=#REF!".  When I try to manually update the cell I get a message that the column is "read only" and cannot be modified.  Any further suggestions?

  25. Mike Thomas says:

    I have another method for accomplishing this.  This method only allows you to calculate the calendar documents for one week from the current day.

    Column 1. – The “Today” column.  You have to create a column of whatever type, but make sure that you call it “Today.”  I just accept all default values.  You will use this column for the next column and then delete it there after.  If you ever have to change another column that depends on the existence of this column you will have to add it back in.  

    Column 2 – The “TodayPlus” column.  This column is used to calculate the end of your week.  Feel free to change from 7 to how ever many days you would like to show your Calendar Events until.  30 or 31 would work too for Monthly Views.  

    =DATE(YEAR(Today), MONTH(Today), DAY(Today)+7)  format as Date and Time – Date Only.

    Now go ahead and delete that “Today” column so your “TodayPlus” column will work.

    Column 3 – This final column is a Yes/No column, call it "IsCurrentWeek."  Based on your Start Time entered of your Calendar Event.  It does a comparison of the previous Column “TodayPlus.”  If your event StartTime is less than seven days from the current day, then it will return a “Yes” value.

    =[Start Time] < TodayPlus

    Finally, on your Calendar view on your home page or wherever you placed the Calendar Web Part, Modify the Shared Web Part and you will be prompted with this window.

    Select “Edit the current view” and then  apply a filter to show items when column “IsCurrentWeek” is equal to “Yes.”

    Done!  Now go to your calendar web part and you should see only events that are happening within the time specified. By default, the Calendar Web Part will not show events that have occurred in the past.  This little feature would have been nice to include though huh?

    Enjoy!!

  26. Clayton says:

    I have tried the above method, however the date of today appears to be 30/12/1899, any ideas?

    We are on WSS 3.0

  27. In other language versions you should also use Today and Me names for columns although Sharepoint UI suggests using local names for these functions!

  28. MattP says:

    With WSS v3.0, I was using SPD workflow to get around this issue.  (note, this may not work for you!)

    Basically, you design a workflow that is "always looping".  

    1. Add a Number column called "heartbeat"

    2. Create a workflow that fires when an item is changed (and put in a way to fire it once).

    3. All this workflow does is wait 8 hours, and then add +1 to the "heartbeat" column value (which kicks off another copy of the workflow)

    The catch?  WSS v3.0 SP1 introduced a new bug which causes SPD workflows which fire "on edit" to fail.  There is also a new problem with the "Wait until…" SPD activity.  So, you will see one of 2 problems:

    1. The "looping" workflow will "complete" after a certain # of loops. (ie – it never fires off after the heartbeat edit)

    2. You will be "Waiting" forever

    MS tells me this will be fixed around SP2 timeframe… we will see.

  29. JeffR says:

    Just tried it with WSS v2.0 and it works fine for me.  It even refreshes.  

  30. MikeD says:

    Regarding Mike Thomas’ illustration above that includes the part:

    Column 3 – This final column is a Yes/No column, call it "IsCurrentWeek."  Based on your Start Time entered of your Calendar Event.  It does a comparison of the previous Column “TodayPlus.”  If your event StartTime is less than seven days from the current day, then it will return a “Yes” value.

    =[Start Time] < TodayPlus

    Where do you enter the formula in a yes/no value column?????? Please help if you know. Thanks

  31. Mike Lin says:

    Formula: DATE(YEAR(Today),MONTH(Today),DAY(Today))

    STEP 3:

    Edit "Today" Column created in STEP 1, and simply delete it.

    STEP 4:

    Create a new column, name it "IsFromToday". We want the answer Yes/No to filter out those "End Time" is later than "current date".

    Create Column > Select Calculated (calculation based on other columns) > Data Type: Yes/No

    Formula: [End Time]>TodayPlus0

    —– PART 2 —–

    STEP 5:

    Create a new column, name it "Today". Just accept all default values (single line of text).

    STEP 6:

    Create a new column, name it "TodayPlus90". This is to define the "current date + 90" date.

    Create Column > Select Calculated (calculation based on other columns) > Data Type – Date & Time, Date Only.

    Formula: DATE(YEAR(Today),MONTH(Today),DAY(Today)+90)

    STEP 7:

    Edit "Today" Column created in STEP 5, and simply delete it.

    STEP 8:

    Create a new column, name it "IsIn90Days". We want the answer Yes/No to filter out those "Start Time" is earlier than "current date + 90" date.

    Create Column > Select Calculated (calculation based on other columns) > Data Type: Yes/No

    Formula: [Start Time]<TodayPlus90

    —– PART 3 —–

    Now it is ready to set filters.

    STEP 9:

    [IsFromToday] [is equal to] [Yes]

    [and]

    [IsIn90Days] [is equal to] [Yes]

    Click OK. You will get a calendar display items from today over the next 90 days!

  32. Mike Lin says:

    Based on Mike Thomas’s method in message (March 5, 2008 2:10 PM), I found the workaround. Steps below are for those who want to show the calendar document in the next 90 days from current date, for example. There are two parts to be done, 1st is to filter out those "End Time" is later than "current date", 2nd is to filter out those "Start Time" is earlier then "current date + 90" date.

    —– PART 1 —–

    STEP 1:

    Create a new column, name it "Today". Just accept all default values (single line of text).

    STEP 2:

    Create a new column, name it "TodayPlus0". This is to define the "current date".

    Create Column > Select Calculated (calculation based on other columns) > Data Type – Date & Time, Date Only.

    Formula: DATE(YEAR(Today),MONTH(Today),DAY(Today))

    STEP 3:

    Edit "Today" Column created in STEP 1, and simply delete it.

    STEP 4:

    Create a new column, name it "IsFromToday". We want the answer Yes/No to filter out those "End Time" is later than "current date".

    Create Column > Select Calculated (calculation based on other columns) > Data Type: Yes/No

    Formula: [End Time]>TodayPlus0

    —– PART 2 —–

    STEP 5:

    Create a new column, name it "Today". Just accept all default values (single line of text).

    STEP 6:

    Create a new column, name it "TodayPlus90". This is to define the "current date + 90" date.

    Create Column > Select Calculated (calculation based on other columns) > Data Type – Date & Time, Date Only.

    Formula: DATE(YEAR(Today),MONTH(Today),DAY(Today)+90)

    STEP 7:

    Edit "Today" Column created in STEP 5, and simply delete it.

    STEP 8:

    Create a new column, name it "IsIn90Days". We want the answer Yes/No to filter out those "Start Time" is earlier than "current date + 90" date.

    Create Column > Select Calculated (calculation based on other columns) > Data Type: Yes/No

    Formula: [Start Time]<TodayPlus90

    —– PART 3 —–

    Now it is ready to set filters.

    STEP 9:

    [IsFromToday] [is equal to] [Yes]

    [and]

    [IsIn90Days] [is equal to] [Yes]

    Click OK. You will get a calendar display items from today over the next 90 days!

  33. Seems that you guys are making this harder than it has to be.  I have SPS 2003.  To view the past 30/60/90 days, this is what I do:

    Create a column called view.  Calculated field, date only.  The formula should be:

    Created+90  or  

    Modified+90   or

    create your own date field called Date; Date+90

    Then set the filter to: view >= [Today]

    That seems to do the trick for me.

  34. Mike Lin says:

    Though the workaround does work the first time, a month later I found the calendar does not update the date of "Today" by itself. It always treat the date on which you create/update the columns (TodayPlus90, TodayPlus0.. etc) as "Today".

    If SharePoint has a system-reserved variable that represents system’s real current date, probably the workaround can be a solution. Otherwise, it seems not a good one. 🙁

    Any idea from anyone?

  35. Christophe says:

    On my blog I show an easier way to achieve the same result as this “Today” trick:

    http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

  36. SharePoint Novice says:

    I am a SharePoint novice, and I’m using WSS 3.0.  I have to come up with a way to only show ONLY the current month’s events in the summary view of the calendar web part.

    I have tried both examples that Mike Lin and Mike Thomas have posted and neither one seems to work for me.

    This is an example of what my output looks like when using Mike Thomas’ example:

    9/1/2008 12:00 AM    Labor Day

    IsCurrentMonth: Yes

    TodayPlus: 9/27/2008

    9/18/2008 12:00 AM    Birthday One

    IsCurrentMonth: Yes

    TodayPlus: 9/27/2008  

    2/1/2010 12:00 AM    Birthday Two

    IsCurrentMonth: Yes

    TodayPlus: 9/27/2008  

    2/3/2010 12:00 AM    Birthday Three

    IsCurrentMonth: Yes

    TodayPlus: 9/27/2008  

    I opted to show the IsCurrentMonth and TodayPlus fields in the sumamry view so that I could verify what value was being returned.

    Does it matter that my events do not have a ‘Start Time’ because they are All Day Events?

    Can anyone help me out with this?  It’s literally driving me insane!  😉

    Thanks!

  37. Benki says:

    Hi all,

    I am using [Today] as a calculated field in one of the column.

    My requirement is I have to list the items Which are due in this month and which are due in next month automatically.

    i have used the confition

    Month[Today] to list the items for the current month &

    MONTH(Today)+1=MONTH([DepartureDate]–> is to list the next month items.

    But every month i have to create [Today] column and delete it so to work this properly. Is there any work around for this? Please let me know asap

    with best regards,

    Benki

  38. A Webb says:

    I am setting up a calendar and I want a list of events for today by the room.

    I would like to either add a sort/filter/group to the Current event function already in SharePoint.

    Is there a way to do this?

    I have found some calcs for showing the event for today but I have many recurring items. These do not show the correct date in the Current Eventy view. They show the first date of the recurring item. (Begin date)

  39. GerryDivya says:

    Hi benki

    I am not able to use Month[Today]…. in my calcukated column(formula)can i know what the problem will be.

  40. Sanooj VS says:

    I created a column Birthday in calenderlist and the type of information column given as "calculated"

    And the formula i given as this

    =IF((DATE(MONTH(Today),DAY(Today))==(DATE(MONTH(DOB),DAY(DOB)))

    and the data type returned from this formula as Yes/No

    But am getting the error is ""The formula contains a syntax error or is not supported.

    Troubleshoot issues with Windows SharePoint Services""

    is this formula is right???

  41. BSNET says:

    Im using the following Round((([END TIME] – [START TIME])*24),1)  to work out the duration of an appointment in a calculated field within a calendar list.  All works well until the user makes the appointmet a reacuring meeting then the furmula works out the entire duration. How can i break this down or divide by the number of occurances

  42. Cris D says:

    How to optionally update a date column:

    Follow Steps in main blog to create the Today column.

    Create two more columns:

    1) Update Modified Date? column:

    Choice: Yes || No :Set default to the desired value (default to update automatically or not)

    2) Calculated column:

    Calculated field:

    =IF([Update Modified Date?],Modified,Today)

    Data to return: Date and Time

    The all the authors need to do is check the box: to select whether to update the field to "today" or leave it as the last set value.

  43. Jen says:

    The work around works fine at first until the next day.  Is there a way to get the deata to "refresh" automatically so that the function continues to work? Thanks!

  44. akshaya P says:

    In my workflow i need to send a mail with date value (19/12/2008),but it displays like

    19/12/2008 12.00AM.

    even while creating itself,i created this DATE column as date only in my list .

    how to display this date without time?

    Plz help me.

  45. Lauren says:

    I know this doesn’t have to deal with the TODAY function, however it is a calculated column with the date format (date only, not time).  I am trying to use this field as the "End Date" in a calendar view of the list, however, I keep getting an error.  Can somebody take a look at my formula and tell me what I am doing wrong?

    =IF(AND(Approval_Status="Approved",[Target Release Date]>0),[Target Release Date],IF(AND(Approval_Status="Pending",[HRSM Desired Release Date]>0),[HRSM Desired Release Date],""))

    *Note: Target Release Date and HRSM Desired Release Date are both "Date" columns in my list.  I use the "" if Approval Status isn’t equal to "Approved" or "Pending," so the Targeted (this formula’s column) Release Date equals nothing instead of the goofy 1899 date SharePoint defaults to.

  46. A common question I get and SharePoint challenge is to show the age of something in SharePoint. A typical

  47. Benjamin Gemperle says:

    You can use the same trick to use the ID of a Lookup Column in a calculated Column.

  48. smvpegb says:

    Thanks a lot you save me a lot of time

  49. João Cabrita says:

    Hey guys, I came up with a solution that will get you the difference between a date (with time also) and correctly take care of some problems I saw in other solutions (for instance having less than a day in hours difference). Hope you will find it useful!

    =IF(HOUR([Ending Date])>HOUR([Starting Date]),DATEDIF([Starting Date],[Ending Date],”d”)&” days “&HOUR([Ending Date]-[Starting Date])&” hours “,(DATEDIF([Starting Date],[Ending Date],”d”)-1)&” days “& HOUR([Ending Date]-[Starting Date])&” hours “)&MINUTE([Ending Date]-[Starting Date])&” minutes”

  50. Joh says:

    Super this saved a boat load of time and effort.

  51. Keith Teach says:

    On the Sharepoint front page we have a web part that we wanted to only show a summary of the calendar and only events for today. After much struggling to get it to show correctly I realized we needed to filter by start time and end time, but could not out of the box. Create a new column called StartDate, made calculated, formula =[Start Time]with data type of Date and Time. Next created column called EndDate, made calculated column, formula =[End Time] with data type of Date and Time. Next, on the calendar web part for the main page applied filter so that StartDate is less than or equal to [today] and EndDate is greater than or equal to [Today]. This works perfect on MOSS 2007.

  52. Ratty says:

    Does anyone know if this has been addressed with SharePoint 2010?

  53. Anto says:

    I’ve tested it with sharepoint v3 and it doesn’t work. The date remains static… Any other idea?

    thanks

  54. Ross says:

    Possible mid way….It’s not perfect, but saves a LOT of time for me.

    Use the TODAY workaround, and when you want to update ALL records, simply recreate the a today column, and then delete it again.

    Seems to work for me and since I use this list once a week for a meeting, this is no major extra work.

    Hope this helps!

  55. shirley says:

    That worked for me.  Thank you!

  56. jon says:

    Thank you so much – that was driving me crazy.

    An insane workaround but it solved my problem, and trust microsoft to make users rely on something like this when it should have been standard functionality

  57. diego says:

    In spanish Today=Hoy, in sharepoint calculated (sapnish) column =hoy work fine

  58. cleanface says:

    Is there anyway to have sharepoint send out emails when an end date is approching?

Skip to main content