Useful Date Formulas
Dates are often important in spreadsheets and there are formulas that help manipulate these. Dates and times are held as numbers with positive integers representing the date and numbers after the decimal point representing the time. Thus the number 42,553.89157 give a date of 02 July 2016 (the 42,553) and a time of 21.23 (the .89157). The dates formulas I use most frequently are:
- = NOW(): produces the current date and time in the cell in which it is entered. NB if you format this to show only the date then this is all you will see.
- =DAY() will extract the day of the month from a date (i.e. between 1 and 31)
- =MONTH() will extract the month number from a date (i.e. between 1 and 12)
- =YEAR() will extract the year from the date e.g. 2016
- =NETWORKDAYS() will tell you the number of working days between two dates i.e. the total number of days less weekends and public holidays. The formula is written as =NETWORKDAYS(startdate, enddate, holidays). Holidays is entered as a range where you have entered the dates of public holidays).
Excel Monkey 