You can't do much in Excel if you store dates and times as text.
When you store them as numbers, though, endless possibilities exist, according to Gini Courter and Annette Marquis of Triad Consulting.
Take advantage of these 14 shortcuts the pros use that will cut your Excel time in half! Download the mp3 of How to Excel at Excel now!
- Add days to a project’s beginning date to calculate the completion date.
- Determine how many days fall between two dates.
- Sort dates in chronological order.
- Subtract a task’s start time from its ending time to determine how long it took to complete the task.
“Excel includes both text and numeric formats for dates and times, but the text formats are mostly for exporting data to other applications,” says Courter. “In Excel, dates and times should be stored as values.”
Here are some of Excel’s most useful date and time functions:
- DATE: Creates a serial value (which you can format using any of the date formats) from three numbers: the year, month and day.
- TIME: Creates a decimal number time from three numbers: hours, minutes and seconds.
- NOW: Returns the serial value for the current date and decimal fraction for the current time from your computer’s system clock.
- TODAY: Returns the serial value for the current date from the computer's system clock.
- DAY: Returns the day number (1 to 31) from a text string, date or serial value.
- MONTH: Returns the month value (1 to 12) from a text string, date or serial value.
- YEAR: Returns the year value (1 to 9999) from a text string, date or serial value.
- HOUR: Converts a text string or decimal fraction to an hour.
- MINUTE: Converts a text string or decimal fraction to a minute.