Looking for solution to date and time problem

[budica]

Hello, I need some help with a little problem I'm having. I have a report that I get once a week and I would like to be able to get a value for the difference in time between two cells. The problem is that this is how the data is placed in the cell.

1/29/2014 12:45:00 AM
1/29/2014 12:48:00 AM
1/29/2014 12:53:00 AM
1/29/2014 1:07:00 AM

So because it's done as both date and time in one cell it won't let me get a serial number for the time so I can do a subtraction and find the difference. Any ideas on this one?

THanks,

[FDibbins]

Hi and welcome to the forum

You need to understand how excel handles dates and times, to figure this out (and its really not that hard)

dates are really just 5-digit numbers representing how many days have passed since 1/1/1900, excel formats them so we recognose dates
Time is a decimal of 1 (day), so 6:00 Am is actually 0.25, 12 noon is 0.5 and so on

So with those date/times above, you should be able to just run the calc, and then format as number of general

[budica]

Hi and welcome to the forum

You need to understand how excel handles dates and times, to figure this out (and its really not that hard)

dates are really just 5-digit numbers representing how many days have passed since 1/1/1900, excel formats them so we recognose dates
Time is a decimal of 1 (day), so 6:00 Am is actually 0.25, 12 noon is 0.5 and so on

So with those date/times above, you should be able to just run the calc, and then format as number of general

That's good to know. Now when I try to do at TIME function it still gives me an error so I must be doing something wrong when trying to get a basic number for the time. When doing TIME wouldn't it be a problem that the date is in the same cell?

[FDibbins]

Perhaps you are subtracting the larger from the smaller? excel doesnt like negative time

	A	B	C
1	Date/Time	A5-A4 etc
2	1/29/2014 0:45
3	1/29/2014 0:48	0.002083	0:03:00
4	1/29/2014 0:53	0.003472	0:05:00
5	1/29/2014 1:07	0.009722	0:14:00

column A is your sample times from above
column B is A5=A4, A4-A3 etc, not formatted (0.002083 of 1 day)
column C is formatted as time...0.002083 now becomes 0:03:00...3 minutes

[budica]

Perhaps you are subtracting the larger from the smaller? excel doesnt like negative time

	A	B	C
1	Date/Time	A5-A4 etc
2	1/29/2014 0:45
3	1/29/2014 0:48	0.002083	0:03:00
4	1/29/2014 0:53	0.003472	0:05:00
5	1/29/2014 1:07	0.009722	0:14:00

column A is your sample times from above
column B is A5=A4, A4-A3 etc, not formatted (0.002083 of 1 day)
column C is formatted as time...0.002083 now becomes 0:03:00...3 minutes

This is what I'm trying to do but I'm having trouble with the syntax for getting the value in column C.

[dredwolf]

How about you show a sample of what you are working with,... we will probably be able too help you much faster, because we can see what is happening for ourselves !

[budica]

Ok, it looks like I found where my problem is. For some odd reason the date/time column actually looks like this

'1/29/2014 00:53
'1/29/2014 00:59

So i have to get rid of the ' at the beginning of the date time. Any advice on that part?

Thanks again for all the help so far!!!

[dredwolf]

Depends on how that column is arrived at, if it is using a formula, then it is simply a matter of removing the ' from the formula, AND making sure the formula is returning a valid date number...again, a sample workbook would help

[budica]

testsheet.xlstestsheet.xls

This is what it spits out to me. I've taken out the sensitive info but this is the column that's giving me trouble.

[FDibbins]

Try this...
=DATEVALUE(A1)+TIMEVALUE(A1)
format custom mm/dd/yyyy hh:mm:ss AM/PM

[budica]

That did it. I can easily just use the value outputs to setup a system for rating the time difference. FDibbins, you rock, and so do all the rest of you. Thanks again for all your help!!!

[FDibbins]

Happy to help