Excel time and date functions

20 Jul 2023   | Computer Stuff · Excel tips and tricks · How to Microsoft Office

Tags: · · · · · · · · · · · · · · · · · · · · · · ·

In this tutorial I am going to discuss Microsoft Excel time and date functions

To see all Excel’s functions related to time and date : Formulas tab (on top ribbon)-Date & Time drop down- select. If you know the syntax just select a cell and start typing then double click the option you want from the list of suggestions. I am going to demonstrate how useful some of popular functions can be in an Excel worksheet.

Worksheet 1

  1. Just type date into the cell. This date will be fixed and will not update as the current date changes this is useful say when adding a date of birth for instance.
  2. Today’s date – =today(), this will be dynamic and change each day, useful if you use this date to calculate eg days to end of month or year.
  3. Today’s date and/or time – =Now(), this will display current date and time in a single cell. If you want time or date only then format cell for time or date. But to get it back to date and time need to: – format cells-custom-d/mm/yyyy h:mm.
  4. Date difference between 2 dates in days, months or years. This formula is not in the formula list but is very useful: Datedif(earliest date,latest date,”Y”) ‘y”=years,  “m” = months or “d’ =days. NB! If you want to drag down your formula then must fix cell range with the current date when you select it in the formula by pressing F4 (fn+f4).
  5. Difference between dates: =Yearfrac(earliest date,latest date) format cell for number with decimal places.
  6. To extract number of days in a specific date: =day (cell with date) so a date like 12/2/2023 will give 12 (days)
  7. Similarly =month(date cell) will give 2 (Feb)
  8. And =year(date cell) will give 2023 (year)
  9.  

Worksheet 2

  1. Days diff-  =end date(2/12/2023)-start date(5/3/2023) =number of days between (272 days). remember answer cell must be formatted for a number. NB start date is not counted but end day is included!
  2. =Edate(date,+/- months) – gives the date when a number of months is added or subtracted. But it is expressed as a date code number; to convert code number to a date – Format-cells-date. To find + or – years from a date need to * months by 12.
  3. =NETWORKDAYS(C3 End,B3start) =195 days Working days only (excl Sat & Sun);. If weekends not Sat & Sun then use =NETWORKDAYS.INTL.

Work days excluding weekends and public holidays: If pub hol is on a normal weekend then not counted!

NB network days function counts the first date and last date.

If you are a casual worker and say only work Mon, Tues and Fri then can customise with with the NETWORKDAYS.INTL. in the space for weekends “0 for working day and 1 for non working day, starting with Monday. Eg if only work Mon, Tues, Fri then:- “0011011”

  • Workday function versus Networkdays function.
    The WORKDAY function returns a date N working days in the future or in the past and you can use it to add or subtract workdays to a given single date. Using the NETWORKDAYS function, you can calculate the number of workdays between two dates that you specify. NB if you load workday from the formulas drop down menu you get this (below) popup to fill in, if you just type it in then you just get the usual arguments list (directly below)
  • Due date- first add or substract days grace to get due date (fixed) =date,days. If only want business days then use =workday(start date, days). If you want to have number of days to due date (dynamic) then =End/due date (static)-current date(dynamic)

Here is the video demo:

Here are some time stamps for browsing the content of this video:

00:00 Beginning

00:18 Excel how to enter a static date

00:42 Access all Excel date and time functions

01:02 Excel current date function =TODAY() – dynamic date

01:41 Excel current date and time function =NOW() – dynamic

02:18 Excel date and time cell formatting

03:47 Excel calculate person’s age in years =DATEDIFF(date1,date2,”Y”) function – dynamic age

05:14 Excel how to fix a date range – F4

06:30 Excel calculate length of service in years with decimal places =YEARFRAC(date1,date2) – dynamic

07:41 Excel how to extract number of days, months or years from a date

09:09 Excel =RIGHT() and =LEFT() functions

10:09 Excel calculate days difference between dates – =date1-date2; =NETWORKINGDAYS(); =NETWORKINGDAYS.INTL() with and without weekends and holidays

14:39 Excel add or subtract days, or months to a given date – =date+/-days; =WORKDAY(date,+/-days); =EDATE(date,number months)

15:50 Excel calculate dynamic days left from a certain date

18:21 Excel convert number format to date format

19:51 Excel current date and time short cuts – CRTL+; and CTRL+SHFT+;

Here are some more Excel video tutorials:

Excel course, free:

Comments are most welcome, if you decide to try this please send us some feedback☺️ Why not visit our channel and browse through our other videos? http://www.youtube.com/c/AnnErasmus

Short Disclaimer/disclosure: My videos/articles are for informational and educational purposes only, you should consult with a professional before attempting anything seen or done in my videos/articles. Please read full disclaimer here: https://basiclowdown.com/category/disclaimer-disclosure/