Wednesday, January 17, 2018

Date-Related Data

As many of us know, Excel stores dates as the number of days elapsed since January 1, 1900, and times are stored as an additional fraction of the date number. Cells will, obviously, display the number or date and/or time, depending on the cell format that you choose.

What is cool, of course, is that you can use Date Functions to perform date calculations and extract the proper date-related data.

The following are Five of the Date-related Functions that are useful to nearly any Excel user:

1.   =DATE(year, month, day)
o   This function returns the Excel date number for the date indicated by the year, month, and day
2.   =YEAR(date) or =MONTH(date) or =DAY(date)
o   Returns the year, month, or day of the date
3.   =TODAY()
o   Returns the current date (as calibrated in the computer system being used)
4.   =WEEKDAY(date, start weekday)
o   Returns the week’s day number for the date specified
5.   =NETWORKDAYs(start, end, holidays)
o   Counts number of days between the start and end dates, excluding Saturdays Sundays and holidays

Handling Dates and Time in Excel can be a bit daunting. If you take a few minutes and master the foregoing Five Functions, however, it can make your Excel life easier.

No comments: