There’s a lot to do TODAY()

The following excerpt is taken from Chandoo’s blog on time and date functions. It’s just a bookmark post. All credits to him.

Staying at present:

• To have latest star date in a cell, just press CTRL+; (of course, in Excel world, star date is nothing but whatever date your computer shows)
  
• To have current time in a cell, just press CTRL+:
  
• Of course, we time travelers are lazy. So pressing CTRL+; every day or CTRL+: every second is not cool. That is why you can use =TODAY() in a cell to get today’s date. It will automatically change when you re-open the file tomorrow.
  
• Likewise, use =NOW() to get current date & time in a cell. Remember, although time changes every second, you will not see the cell updated unless the formula is somehow re-calculated. This is done by,
  
  • Pressing F9
    
  • Saving / re-opening the file
    
  • Making any changes to any cell (like typing a value, changing a value)
    
  • Editing the formula cell and pressing Enter
    
• To check if today is after or before the date in cell A1, you can use =TODAY() > A1. This will be TRUE if A1 has a past date and FALSE if A1 has a future date.
  
• To know how many days are there between TODAY and the date in A1, use =TODAY() – A1. This will be a negative number if A1 is a future date. To see just the number of days (without negative sign), you can use =ABS(TODAY()-A1)
  
• To know how many hours are left between the time in A1 and current time, use =(NOW()-A1)*24.
  
• While the above formula works, it shows hours and fraction. To just see hours and minutes left, you can use =TEXT((NOW()-A1), “[hh]:mm”). Note: This formula works only when A1 < NOW().
  
• To know how many weeks are left between TODAY() date and a future date in A1, use =(TODAY() –
  A1)/7
  
• To know how many months are left between TODAY() and date in A1, use = DATEDIF(TODAY(), A1, “m”).
  Related: How to use DATEDIF function.
  
• To know which month is running, use =MONTH(TODAY())
  
• To see the month name instead of number, use =TEXT(TODAY(), “MMMM”). This shows the month’s name in your Excel language.
  
• To know which year is running, use =YEAR(TODAY())
  
• To see the last 2 digits of the year, you can use =RIGHT(YEAR(TODAY()), 2)
  
• To find the day of week for TODAY, use =WEEKDAY(TODAY()). This will give a number (1 to 7, 1 for Sunday, 7 for Saturday).
  
• To see the weekday name instead of number, use =TEXT(TODAY(), “DDDD”).
  
• To see today’s date alone, use =DAY(TODAY())
  
• To know if the present year is a leap year or not, see this.
  

Going back in time

1. To go back by 6 days from the date in A1, use =A1-6
   
2. To go back to last Friday use =A1-WEEKDAY(A1, 16). This works in Excel 2010, 2013. If your time machine is old (ie you have Excel 2003 or earlier versions), you can use =A1-CHOOSE(WEEKDAY(A1), 2,3,4,5,6,7,1)
   
3. To go back by 5 weeks, use =A1-5*7
   
4. To go back to start of the month, use =DATE(YEAR(A1), MONTH(A1),1)
   
5. To go back to end of previous month, use = DATE(YEAR(A1), MONTH(A1),1) – 1
   
6. Or use =EOMONTH(A1,-1)
   
7. To go back by 2 months, use =EDATE(A1, -2)
   
8. To go back by 27 working days, use =WORKDAY(A1, -27). This assumes, Monday to Friday as working days.
   
9. To go back by 27 working days, assuming you follow Monday to Friday work week and a set of extra holidays, use =WORKDAY(A1, -27, LIST_OF_HOLIDAYS)
   
10. To go back by 7 quarters, use =EDATE(A1, -7 * 3)
    
11. To go back to the start of the year, =DATE(YEAR(A1), 1,1)
    
12. To go back to same date last year, = DATE(YEAR(A1)-1, MONTH(A1), DAY(A1))
    
13. To go back a decade, =DATE(YEAR(A1)-10, MONTH(A1), DAY(A1))
    

Going forward in time

We, time travelers are smart people. Once you know that turning the knob backwards takes you to past, you know how to go to future. So I am giving very few examples for going forward in time.

1. To go to the 17th working day from date A1, assuming you use Sunday to Thursday workweek, use =WORKDAY.INTL(A1,17,7). This formula works in Excel 2010 or above.
   
2. To go to next hour, use=A1+1/24
   
3. To go to next day morning 9AM, use =INT(A1+1) + 9/24
   
4. To go to 18th of next month, use =DATE(YEAR(A1), MONTH(A1)+1, 18)
   
5. To go to end of the current quarter for date in A1, use =DATE(YEAR(A1), CHOOSE(MONTH(A1), 4,4,4,7,7,7,10,10,10,13,13,13),1)-1
   
6. To go to a future date that is 4 years, 6 months, 7 days away from A1, use =DATE(YEAR(A1)+4, MONTH(A1)+6, DAY(A1)+7)
   

Finding the amount of time traveled

1. To know how many days are between 2 dates (in A1 & A2), use =A1-A2
   
2. To know how many working days are between 2 dates, use =NETWORKDAYS(A1, A2) (remember: A1 should be less than A2).
   

Fixes for common time travel hiccups

1. If you see ###### instead of a date in a cell, try making the column wider. If you still see ######, that means the date value is not understandable by Excel (negative numbers, dates prior to 1st of January 1900 etc.)
   
2. Often when pasting date values in to Excel, you notice that they are not treated as dates. Use these techniques to fix.
   
3. If you pass in-correct values or use wrong parameters, your date formulas show an error like #NUM or #VALUE. Read this to understand how to fix such errors.