Time and Date Functions in Excel: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 31: Line 31:


  =A1 + TIME(1, 35, 10)
  =A1 + TIME(1, 35, 10)
=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=
https://home.feodorov.com:9443/wiki/Wiki.jsp?page=ExcelTextFunctions#CombineTextWithDateOrTime
=Extract Time/Date Information from a String=
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())

Revision as of 21:45, 14 December 2017

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)

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

https://home.feodorov.com:9443/wiki/Wiki.jsp?page=ExcelTextFunctions#CombineTextWithDateOrTime

Extract Time/Date Information from a String

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())