Excel Date Time Conversion

Excel Date Time Conversion

Excel has a special way to treat Date Time value but most people are not aware of that. You need to understand the meaning of the numeric values that represent Excel Date Time in order that you can freely perform Excel date time conversion to hours, minutes, days,  etc. After you understand the meaning of the numerical values, you can easily calculate Excel Date Time Difference.

Also read the below post about calculate time difference

Excel time difference in hours minutes seconds

Excel Date Mechanism

Each date you enter can be represented by a numeric value. For example, type 1/1/2014 in a Cell and then go to Format Cells, change Category to “General”.

Excel_Time_02

1/1/2014 becomes 41640, now try 1/1/1900 (Jan 1)

1/1/1900 becomes 1

2/1/1900 becomes 2

Now you see Excel views 1/1/1900 as the first day, and add one day up until 1/1/2014, a total of 41640 days.

You can verify it by using DATEDIF Function to find the date difference between these two days.

=DATEDIF("1/1/1900","1/1/2014","D")

The formula returns 41639, which is 1 day less, because DATEDIF function counts 2/1/1900 as the first date difference, but you see my point.

Excel Time Mechanism

If you don’t specify a date when you enter a time in a Cell, you will see the default date is 0/1/2014 (Jan 0) if you change the Format to Date Time or Date.

To illustrate Excel Time, take 8:00 AM as an example. Type 8:00:00 in a Cell, then go to Format Cells, change Category to “General”.

Now the Cell display 0.33333, so why is that 0.33333 represents 8:00:00?

It is not difficult to understand, you see Excel converts date to an integer, then numeric value of time must be in form of day as well.

Therefore 0.33333 means 8 hours (1/3 day). Let take some more examples below,

For 0:00:00, day value is 0

For 24:00:00, day value i s 1

Conversion of Excel Time

Base on the fact that 1 day = 24 hours, we can convert time to hours by multiplying the numeric value by 24. We want to do this because sometimes we need the display time and minutes as hours in decimal places.

0.3333*24 = 8 (hours)

To convert to minutes, multiple the above value by 60

0.3333*24 *60 = 480 (minutes)

To convert to seconds, multiple the above by 60 again

0.3333*24 *60*60 = 28800 (seconds)

Outbound References

http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

One thought on “Excel Date Time Conversion

Leave a Reply

Your email address will not be published.