Often when we think about functions in Excel, it’s about working out a calculation. But what if you need to deal with dates and times? Well, there are a few available, and I’m going to show how to use date and time functions in Excel 365.
Date Functions
Let’s start with the date functions.
One thing to be aware of, the date that we are all used to – Day, Month, Year, isn’t how Excel stores the date. It’s actually a number. I’m sure that there is a reason or formula, but as it’s usually hidden, I haven’t been bothered about how it’s worked out.
If you want the official blurb:
Microsoft DATEVALUE function
- Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
- Most functions automatically convert date values to serial numbers.
Just be aware, if you’ve typed in a number, and it looks ‘weird’, check the formatting.
Time to take a look at the functions: 😋
Now()
Based on the name alone, you should be able to guess that this function adds the current date and time into a cell.
You can apply formatting to choose if it’s the date or time (or leave it as both).
One little catch – when you open your spreadsheet, the function will be updated so that it’s always displaying the date/time now.
Only use it when you need a current and updated date or time.
Today()
Similar, in a way to now, the Today function displays the current date.
Just like Now, Today will be updated to show today’s date when you open or update the spreadsheet.
Days(end_date, start_date)
Potentially one of the more useful Date and Time functions.
Given a start and end date, Days will work out the difference (in days) between them.
Note that the function wants the end date first! 😅
If you’ve got a delivery schedule, or need to keep a track how long something has taken (most often travel), then this function will do all the hard work for you.
Days360(start_date, end_date, [method])
Similar to Days, this version works on the assumption that across 12 months there are 30 days. Which explains why it’s called 360 and not 365.
I’m not going to explain the date requirements, but what I will mention is method.
First it’s in square brackets so that means it’s optional. (The same as in Excel IF Statement: What do you need to know)
The two options are:
False US (NASD) method – NASD stands for National Association for Securities Dealers.
This reads in Excel’s help as quite convoluted, I’ll try and ‘translate it’.
If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.
Microsoft Support – DAYS360 Function
It’s all based on the date.
We know:
30 days have September, April, June and November
All the rest have 31, except February alone which has 28 and 29 each leap year.
For the start date, if it’s the 31st, then in the function it becomes the 30th.
For the end date 😕 – if the start date is the end of a month, then the end date becomes the first of the following month
e.g. Start = Jan 15th, and the end date is Mar 31st, then the End Date (in the function) = Apr 1st.
True (Europe) – if the date selected is on the 31st, the function reads it as the 30th day of the month.
Day(serial_number)
Given a date (the serial number), the Day function will display the day, as a number.
Month(serial_number)
Yep, you’ve guessed it, Month shows the month of the given date as a number.
Year(serial_number)
I’m sure you’re not surprised to learn that Year will display the Year from a given date.
Use of Date Functions
After all of those functions, you’re probably wondering what use are they?
I mean, you can type in the date, or the day, month or year pretty easily yourself. But what if you’ve got hundreds or thousands of lines to work through?
With the Days function I can see a use for it – it you need something to arrive by a specific date, work out based on the skipping when you need to order it.
Note: Personally, I’d always order things with as much leeway as possible – we never know how long the shipping really takes.
The one I’m really not convinced about is Days360. If you’re working things out, the difference of 5 days could make trouble.
Time Functions
There doesn’t feel like there are as many functions related to time as there are for dates, however some of the date options (e.g. Now) includes time.
To type a time into Excel, use the colon (:) to separate out the hours, mins and seconds.
Time(hour, minute, second)
Use this function to pull together a three part time. The displayed time is in 12-our format.
Note: You need to include all three parts for the function to work.
Hour(serial_number)
No points for guessing that the Hour function will display just the hour from a given time.
This in in 24hr format.
Minute(serial_number)
Likewise, it’s hard to get Minute wrong, but just in case…
It displays the minutes in a given time.
Second(serial_number)
If you work in seconds (which if you’re timing something you might), then the Seconds function shows that part of the time.
When to use Time Functions
Like the other functions, when it pulls out a specific part of the time, it can be useful to ‘automate’ parts of your spreadsheet.
For the function called ‘Time’, I’m not sure where it could be useful. We know that the point is that you can replace the information with a cell reference.
These are functions that I’ve ever needed to use, so I can’t offer any suggestions. If you have a use example share it in the comments.
Next Steps
Now you know how to use date and time functions in Excel. Whether you want a fixed date/time, or one that will update each time that you open the file, there’s a function for you.
Related Posts
- Complete List Of Options Where You Can Customise the Colour
When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour - How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - What’s the point of the Excel Information Functions?
What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more. - How to use Date and Time Functions in Excel
Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained. - VLookup in Excel: What Do You Need to Know Step by Step
Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics!
how to use date and time function in excel, excel insert date and time function, date and time functions in excel