Time and Date Functions in Excel

From NovaOrdis Knowledge Base
Jump to navigation Jump to search

External

Internal

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"

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())
Retrieved from "https://kb.novaordis.com/index.php?title=Time_and_Date_Functions_in_Excel&oldid=67979"