Changing Cell Format

[momo123]

hi..i have a cell which has a time format.

but currently its displaying 2.00907E+13.

is it possible that i can convert it into the normal time format?

for example B2 in my sample is 20090718170156 which actually means 2009/07/18 17hrs 01min(s) 56Sec(s)

and after converting, how can i get the time difference between start time and end time?

P.S i tried doing a manuel format cells, but it didn't seem to work.

[Paul]

Hi Momo,

Try using two helper columns. For example, insert two blank columns after column C. In D1 and E1 put 'New Start Time' and 'New End Time'.

In D2 put the formula:

=DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))+TIME(MID(B2,9,2),MID(B2,11,2),RIGHT(B2,2))

Copy that formula to E2, then copy both down to row 23. Select cells D2:E23, right-click on the selection and choose Format Cells. In the formatting window select Custom, then in the Type box put the custom format: yyyy/mm/dd hh:mm:ss
Click OK

In F2 (your time difference column) you then just need to use a simple subtraction formula, e.g.

=E2-D2

Fill that down to F23 and you're done! ( You may need to format F2:F23 as [h]:mm:ss )

[momo123]

thank you! its working =)

but do you mind explaining this part?

=DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))+TIME(MID(B2,9,2),MID(B2,11,2),RIGHT(B2,2))

[Paul]

I'll give it a shot...

Your cells are laid out as 20090718170156, which is YYYYMMDDhhmmss (Y=Year, M=Month, D=Day, h=Hour, m=Minute, s=Second)

Dates in Excel are not actually stored as "mm/dd/yyyy" or "dd/mm/yyyy", they are stored as an integer (whole number) that is ## days from Excel's "start" date of January 1, 1900.

Times in Excel are not stored as "hh:mm:ss", rather they are decimal numbers that represent the fraction of the day. For example, 12 noon is stored as 0.5. 12:01:18 AM is stored as 78 seconds divided by 86,400 seconds in a day, or 0.00090278.

Dates and times, when combined (or added together) represent a date-time value. For example: August 10, 2009 11:17:04 AM is stored in Excel as a number, 40035.47019, or 40035 days since 1/1/1900, plus 0.47019 days worth of seconds (11 hours 17 minutes 4 seconds = 40624 seconds / 86400 seconds per day = .47019).

Now that we have that covered, in order to perform math on dates you must first convert the strings (20090718170156) to date and time values (xxxxx.yyyyy).

=DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))+TIME(MID(B2,9,2),MID(B2,11,2),RIGHT(B2,2))

The DATE function takes three arguments, =DATE(YEAR(),MONTH(),DAY()). Using the LEFT and MID functions, I simply pulled those sections of the string into the correct place. LEFT(B2,4) pulls the 4 left-most characters in your string, and since it's in the first argument of the DATE function it represents the YEAR.

The TIME function also takes three arguments, =TIME(HOUR(),MINUTE(),SECOND()). The MID functions do the same thing, pulling the correct digits into the correct arguments.

Once that is done, it's just a matter of adding the DATE() function to the TIME() function to generate an actual Date-Time string and then perform math on it.

Hope that helps explain it!