Time and Date Functions in Excel

External

• http://excelsemipro.com/2010/08/date-and-time-calculation-in-excel/
• http://www.cpearson.com/excel/datearith.htm

Internal

• Excel

Overview

Excel maintains Data and Time values internally as number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: dddd.tttttt. This is called a serial date, or a serial date-time.

Dates

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544 since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1.

Time

The fractional portion of the number, ttttt, represent the factional portion of a 24 hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly 6PM is stored as 0.75, or 75% of a 24 hour day. Any date and time can be stored as the sum of the date and the time. For example, 3 PM on 19-Jan-2000 is sore internally as 36544.625. When you enter a time without a value, such as entering 15:00 into a cell, the date portion is zero. Zero indicates that there is no date associated with the time. You should remember that entering just a time does not automatically put in the current date.

Date and Time Arithmetic

Add a Time Interval to a Date

To add 1 hour, 35 minutes and 10 seconds to a time in A1, use the function:

=A1 + TIME(hours, minutes, seconds)

Example:

=A1 + TIME(1, 35, 10)

Add a Number of Months to a Date

=EDATE(start_date, number_of_months)

Is the Content of a Cell a Date?

Check whether the content of a cell is or not a date

=ISERR(DATEVALUE(A1))

If the content is a date, will return FALSE.

Combine Text with Date or Time

="something happened on "&TEXT(A1,"mm/dd/yyyy")

Extract Time/Date Information from a String

ISO 8601

Assuming a timestamp "2020-07-16T23:36:41.000Z", the formula to convert to Excel time is:

=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

Arbitrary Format

If we have "Thu Nov 17 00:00:00 PST 2011" stored in A1, then we can extract time with =TIMEVALUE(MID(A1, 12, 8)).

Then we can format the result cell as "Time".

In order to extract date, we can use =DATEVALUE(MID(A1, 5, 6)). However, be careful that the date is relative to the current year, not 2011.

Calculate Elapsed Time for Time Reports

1. Format "Start" Column as "Time, 1:30 PM". Let's assume the column is A.

2. Format "End" Column as "Time, 1:30 PM". Let's assume the column is B.

3. Calculate the difference with:

=VALUE(MID(TEXT($B1-$A1, "h:m"), 1, SEARCH(":", TEXT($B1-$A1, "h:m")) - 1)) + (VALUE(MID(TEXT($B1-$A1, "h:m"), SEARCH(":", TEXT($B1-$A1, "h:m")) + 1, 100)) / 60)

4. Format the result cell as "Number, 2 Decimal Places"

Display the Difference in Days between Two Dates

=B2-B1

Handling Milliseconds in Excel

In order to get Excel understand milliseconds in a CSV, enter the time as follows:

04/29/13 09:56:51.400

To format milliseconds:

hh:mm:ss.000

Display Day of the Week

Enter a date then format it as such: Custom -> "ddd"

Display Week in Year

=WEEKNUM(NOW())

Display Current Year

=YEAR(TODAY())