Jun 5 2007

Today() Formula in Excel

A common excel formula that can come in handy in many projects with Excel is the Today() Function. The Today() Function is used to return the serial number of the current date. By formatting the cell where the formula is used, it will return the Month, Day and Year of today. One of the easiest functions in Excel to use, here is the formula:

=Today()

Since today is June 5, 2007, the formula will return 6/5/07 since it is automatically formatted in date format. If you were to change the formatting to a number, the formula would return the serial number 39238 (If you are wondering what this number stands for, it is the number of days from January 1, 1900 – Tomorrow the serial number returned would be 39239)

There are many places in your projects you will be using the Today() function, from showing the date your spreadsheets were last updated to calculated how many days from today a deadline is. Once you learn to use this function regularly it will be a function you can’t live without.

Over the next few posts, I will be adding many example of how to use the numerous date and time functions built into excel. Please contact us with any questions or suggestions you may have.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

17 Comments on this post

Trackbacks

  1. mele said:

    Thanks for posting this, I had been trying to find this info all morning.
    I haven’t been able to find a formula that allows me to add one week to the =TODAY date, and have both of them automatically update in the worksheet. Here we go:

    A1 B1
    =TODAY() “today’s” date plus 7 days

    As usual the MS Office help site is leadin gme down endless rabbit holes. I wonder if you can lead me in the right direction.
    Thanks!

    February 6th, 2009 at 1:10 pm
  2. john said:

    Hi Mele,

    Have you tried just entering =TODAY() +7 into a cell? That should give you the result your looking for and update automatically with the changing of the days.

    February 6th, 2009 at 6:30 pm
  3. flapep2 said:

    Thank you all for your info. I have been using Lotus 123 and just changed to Excel. There is quite a difference, esp for a novice such as myself. This info has helped me as I have been searching for it a long time.

    July 30th, 2009 at 9:38 am
  4. john said:

    Thanks flapep2… I’m glad you are finding the info helpful. If there is anything you would like to see posted on Excel Hints, please drop me a note.

    July 30th, 2009 at 11:19 am
  5. Tom said:

    John and team, I am having a problem with the SUMIFS function when comparing with a criteria < today(). have you encountered this before ?

    for accounting purpose (the TO DATE totals for example) I am trying to get the Sum of the data that falls before today.

    August 19th, 2009 at 12:34 pm
  6. john said:

    Hi Tom, Can you put your formula in a comment and I’ll take a look at what’s not working for you?

    Thanks,
    John

    August 19th, 2009 at 2:55 pm
  7. Tom said:

    =SUMIFS($S8:$QT8,$S$7:$QT$7,G$7,$S6:$QT6,”<TODAY()")

    for some reason the < operation in my workbook is not working with the volatile today()

    it works with straight equals to today() …… or the < with entering a straight date serial number.

    but not the combo of < and today(). Thanks, Tom

    August 28th, 2009 at 11:05 am
  8. Tom said:

    Hey John — just checking in on how to sum “To Date” values

    September 2nd, 2009 at 11:51 am
  9. Eric said:

    I dont get this I tried
    =TODAY()
    at work on Version 7 for windows 95, (why it is so old, dont ask)
    but all I get in the cell is =TODAY() , it works great at home with openOffice

    February 16th, 2010 at 5:11 am
  10. Eric said:

    OK I got action.
    There must of been some kind of “lock” on the spreedsheet,
    When I opened Excel this morning, I tryed it on a blank sheet and it worked, so then I went back to my other file I could not get it to work.

    And then copy & paste information to a new file and saved it under the old file name , that is one cool way to fix the problem,

    It would great to know how I could of “unlocked” the speedsheet.

    February 17th, 2010 at 1:31 am
  11. Chaz said:

    Since I handle Bills with close dates, I need a formula that will automatically tell me how many days are left before the deadline. I normally have to place the deadline date to make a simple equation but it there a way that i can make a formula that says the current month instead of the exact date? i need it to tell the month today in this format, mm/dd/yyyy. for example, if i launch the file today, it should reflect 10/1/2011 regardless of what date it is but when I launch it anytime next month it should reflect 11/1/2011.

    October 13th, 2011 at 3:36 pm
  12. john said:

    Try using a formula such as this: =date(year(A1),month(A1),1)

    October 13th, 2011 at 8:59 pm
  13. Myron Murray said:

    I have an XLS w/multiple tabs. Some I update regularly, some not. Similar to the today() function, is there a function whereby if I update just one of the multiple tabs, it will only change the date in the specific cell with “today’s” date?

    January 16th, 2012 at 8:10 pm
  14. Joyce said:

    =COUNTIF(B6:B771,”<today()")

    for some reason the < operation in my workbook is not working with the volatile today()

    it works with straight equals to today() …… or the < with entering a straight date serial number.

    but not the combo of < and today().

    Please help. My issue is the same as Tom's above question.

    September 9th, 2013 at 12:58 pm
  15. rocky said:

    Hi,

    When I use the following forumula, TODAY function, I get returned a date value of 0/01/1900 when I am expecting it to be today, i.e. 31/10/2013. What am I doing wrong?

    Here is my formula in cell M34:
    =IF(K34=ISBLANK(K34),TODAY(),K34)

    K34 contains a proper date in the above format or blank.

    Thanks.

    October 30th, 2013 at 7:39 pm
  16. rocky said:

    Hi,
    Looks like I found the problem for the above date problem.

    I should be testing for null by “” rather than “=ISBLANK(K34)” in the above formula.

    It works.

    October 30th, 2013 at 8:36 pm
  17. porcelana lubliana said:

    What’s up, just wanted to mention, I enjoyed this blog post.
    It was helpful. Keep on posting!

    May 3rd, 2016 at 9:38 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives