Excel time and date functions

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)
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”
4. 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.
5. 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 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 is a link to the blog article:
https://basiclowdown.com/excel-time-a...

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?    / 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/dis...