Excel Formatting Issue

[jsmith1313]

I received a large amount of data sent to me in an Excel spreadsheet. The data cells represent days with time in the format below.

02 03:33:22 (the day being the 2nd, and then hours, minutes, seconds) I need to be able to calculate the difference in these values in Excel. For example the difference between;

02 03:33:22 and 02 04:44:23

The cells are formatted to general. I am having trouble getting Excel to recognize the data as a day with GMT time. I made the custom format of "dd hh:mm:ss" for the cells and used the formula "=ABS(A1-B1)" to get the difference. This leads to an error.

However when I go to the cells and put a pretend month with a dash and click out for example "6/02 03:33:22" the cell recognizes that this should be a date and time and the formula works. I have thousands of cells and therefore cannot do this for each one. It appears Excel is having trouble recognizing the date custom format until I add a month.

I should note that I have Excel 2008 for the Mac.

Any thoughts/help would be appreciated.

[mrice]

Try

C1

=INT(LEFT(A1,2)) +TIMEVALUE(RIGHT(A1,8))

D1

=INT(LEFT(B1,2)) +TIMEVALUE(RIGHT(B1,8))

E1

=D1-C1

Format E1 as

hh:mm:ss

[jsmith1313]

That seems to be doing the trick. I combined the formula to read;

=(INT(LEFT(B1,2))+ TIMEVALUE(RIGHT(B1,8))) - (INT(LEFT(A1,2)) + TIMEVALUE(RIGHT(A1,8)))

Should I be concerned that the formula may not reflect the difference between entries crossing over into new days properly;

02 23:45:22 and 03 00:46:22

They look like they are processing correctly but I figured I would ask considering there are tens of thousands of rows and I can't control check all of them.

Thanks for the help, this was a much quicker fix then what I was previously trying to create.

[mrice]

Should be OK provided that you do not have examples where the day reduces because of a new month. As months have different lengths, there would be ambiguity.

[joeu2004]

I received a large amount of data sent to me in an Excel spreadsheet. The data cells represent days with time in the format below.

02 03:33:22 (the day being the 2nd, and then hours, minutes, seconds) I need to be able to calculate the difference in these values in Excel.

If the days is always 2 characters and the time is always 8 characters, and A1 is the later time:

=LEFT(A1,2)+RIGHT(A1,8) - (LEFT(B1,2)+RIGHT(B1,8))

formatted as Custom [h]:mm:ss . [h] will display durations of 24 hours or more.

put a pretend month with a dash and click out for example "6/02 03:33:22"

Such tricks work only if the number of days is 31 or less. (Actually, 30 or less if you use "6/", which is June.) In the form m/dd, "dd" is day of the month, not a number of days.

I made the custom format of "dd hh:mm:ss" for the cells

That does not work because "02 03:33:22" is text, not numeric. Besides, "dd" is interpreted as day of the month, not a number of days. So again, that would work (with numeric values) only if the number of days is 31 or less.

[jsmith1313]

Should be OK provided that you do not have examples where the day reduces because of a new month. As months have different lengths, there would be ambiguity.

There are some examples where the day reduces because of a month transition. Any possible tweaks to the formula that would take those possibilities into consideration?

[joeu2004]

There are some examples where the day reduces because of a month transition. Any possible tweaks to the formula that would take those possibilities into consideration?

If we can assume the earlier date (B1) is either the last day of the previous month or an earlier day in the same month as the later date (A1), the following should suffice:

=LEFT(A1,2) - LEFT(B1,2)*(LEFT(A1,2)-LEFT(B1,2)>=0) + RIGHT(A1,8) - RIGHT(B1,8)

If we cannot make that assumption -- for example, 27 12:34:56 and 02 01:02:03 -- we cannot make any adjustment without knowing both months (or one month, if at least we can assume consecutive months).

Also, that will not work if the days are intended to be in separate months, and the day in A1 is greater than the day in B1. For example, (Feb) 28 12:34:56 and (Mar) 30 01:02:03.

Also note the unstated assumption of the previous suggestions and this one: 24-hour time is specified. For example, 13:45:27 for 1:45:27 PM.