date to time formula

[Brownie_D75]

I need a formula to shows hours between dates.

Example:
Truck arrived @ 22:00. It arrived on 7/28/09. As of 7/30/09 @ 05:30, how
many hours has that truck been here?

 

[Eduardo]

Hi,
See CPearson web has the solution you are looking for with examples

http://www.cpearson.com/excel/DateTimeWS.htm

if this helps please click yes, thanks

 

[Rick Rothstein]

If your date/time values are in one cell (each) as real Excel date and
times, then...

=24*(B1-A1)

assuming your later date time value is in B1 and your earlier one is in A1.

 

[Brownie_D75]

This is where it get a little complicated. The hour the truck arrived is in
military (24:00) in one cell. Then the date is in another. I have to manually
enter the current date & 05:30. I need to calculate those total hours (hours
& minutes are ok too). When I calulated it with the below formula, it only
came up with 13:36. (Truck arrived at 24:56 on 7/28/09. As of 05:30 on
7/30/09 = ??? hours?

 

[Jacob Skaria]

A.Date = Arrived Date
AODate = As of Date

ColA ColB ColC ColD ColE
A.Date A.Time AODate AOTime Total Hours
7/28/2009 23:56 7/29/2009 5:30 5:34

Total hours in cell E2
=(C2+D2)-(A2+B2)
and custom format the total cell as below..
[h]:mm

If this post helps click Yes

 

[Brownie_D75]

I think this formula is missing something. If I put in my data for this
formula, it come up to 4:34. My data says arrival date is 7/28 at midnight &
then date I want the information for is 7/30 at 05:30. There is more than 4
1/2 hours between 7/28 & 7/30.

A.Date = Arrived Date
AODate = As of Date

ColA ColB ColC ColD ColE
A.Date A.Time AODate AOTime Total Hours
7/28/2009 23:56 7/29/2009 5:30 5:34

Total hours in cell E2
=(C2+D2)-(A2+B2)
and custom format the total cell as below..
[h]:mm

If this post helps click Yes
---------------
Jacob Skaria

This is where it get a little complicated. The hour the truck arrived is in
military (24:00) in one cell. Then the date is in another. I have to manually
enter the current date & 05:30. I need to calculate those total hours (hours
& minutes are ok too). When I calulated it with the below formula, it only
came up with 13:36. (Truck arrived at 24:56 on 7/28/09. As of 05:30 on
7/30/09 = ??? hours?

 

[Jacob Skaria]

Did you custom format the total cell as [h]:mm

Right click>FormatCells>Custom>Type:=
[h]:mm

--
If this post helps click Yes
---------------
Jacob Skaria

I think this formula is missing something. If I put in my data for this
formula, it come up to 4:34. My data says arrival date is 7/28 at midnight &
then date I want the information for is 7/30 at 05:30. There is more than 4
1/2 hours between 7/28 & 7/30.

A.Date = Arrived Date
AODate = As of Date

ColA ColB ColC ColD ColE
A.Date A.Time AODate AOTime Total Hours
7/28/2009 23:56 7/29/2009 5:30 5:34

Total hours in cell E2
=(C2+D2)-(A2+B2)
and custom format the total cell as below..
[h]:mm

If this post helps click Yes
---------------
Jacob Skaria

This is where it get a little complicated. The hour the truck arrived is in
military (24:00) in one cell. Then the date is in another. I have to manually
enter the current date & 05:30. I need to calculate those total hours (hours
& minutes are ok too). When I calulated it with the below formula, it only
came up with 13:36. (Truck arrived at 24:56 on 7/28/09. As of 05:30 on
7/30/09 = ??? hours?

:

If your date/time values are in one cell (each) as real Excel date and
times, then...

=24*(B1-A1)

assuming your later date time value is in B1 and your earlier one is in A1.

--
Rick (MVP - Excel)


I need a formula to shows hours between dates.

Example:
Truck arrived @ 22:00. It arrived on 7/28/09. As of 7/30/09 @ 05:30, how
many hours has that truck been here?

 

[Rick Rothstein]

It is always a good idea to show us an example of your layout. Assuming your
start date and time are in A1 and B1 and your end date and time are in C1
and D1...

=24*((C1+D1)-(A1+B1))

 

[Brownie_D75]

Yes I did & it still calculates as 4 hours. Where did the other 29 hours go?

Did you custom format the total cell as [h]:mm

Right click>FormatCells>Custom>Type:=
[h]:mm

--
If this post helps click Yes
---------------
Jacob Skaria

I think this formula is missing something. If I put in my data for this
formula, it come up to 4:34. My data says arrival date is 7/28 at midnight &
then date I want the information for is 7/30 at 05:30. There is more than 4
1/2 hours between 7/28 & 7/30.

A.Date = Arrived Date
AODate = As of Date

ColA ColB ColC ColD ColE
A.Date A.Time AODate AOTime Total Hours
7/28/2009 23:56 7/29/2009 5:30 5:34

Total hours in cell E2
=(C2+D2)-(A2+B2)
and custom format the total cell as below..
[h]:mm

If this post helps click Yes
---------------
Jacob Skaria


:

This is where it get a little complicated. The hour the truck arrived is in
military (24:00) in one cell. Then the date is in another. I have to manually
enter the current date & 05:30. I need to calculate those total hours (hours
& minutes are ok too). When I calulated it with the below formula, it only
came up with 13:36. (Truck arrived at 24:56 on 7/28/09. As of 05:30 on
7/30/09 = ??? hours?

:

If your date/time values are in one cell (each) as real Excel date and
times, then...

=24*(B1-A1)

assuming your later date time value is in B1 and your earlier one is in A1.

--
Rick (MVP - Excel)


I need a formula to shows hours between dates.

Example:
Truck arrived @ 22:00. It arrived on 7/28/09. As of 7/30/09 @ 05:30, how
many hours has that truck been here?

 

[Jacob Skaria]

The formula will work with the below data; only if the dates and time are in
excel date/time formats...

If this post helps click Yes
---------------
Jacob Skaria

Yes I did & it still calculates as 4 hours. Where did the other 29 hours go?

Did you custom format the total cell as [h]:mm

Right click>FormatCells>Custom>Type:=
[h]:mm

--
If this post helps click Yes
---------------
Jacob Skaria

I think this formula is missing something. If I put in my data for this
formula, it come up to 4:34. My data says arrival date is 7/28 at midnight &
then date I want the information for is 7/30 at 05:30. There is more than 4
1/2 hours between 7/28 & 7/30.

:

A.Date = Arrived Date
AODate = As of Date

ColA ColB ColC ColD ColE
A.Date A.Time AODate AOTime Total Hours
7/28/2009 23:56 7/29/2009 5:30 5:34

Total hours in cell E2
=(C2+D2)-(A2+B2)
and custom format the total cell as below..
[h]:mm

If this post helps click Yes
---------------
Jacob Skaria


:

This is where it get a little complicated. The hour the truck arrived is in
military (24:00) in one cell. Then the date is in another. I have to manually
enter the current date & 05:30. I need to calculate those total hours (hours
& minutes are ok too). When I calulated it with the below formula, it only
came up with 13:36. (Truck arrived at 24:56 on 7/28/09. As of 05:30 on
7/30/09 = ??? hours?

:

If your date/time values are in one cell (each) as real Excel date and
times, then...

=24*(B1-A1)

assuming your later date time value is in B1 and your earlier one is in A1.

--
Rick (MVP - Excel)


I need a formula to shows hours between dates.

Example:
Truck arrived @ 22:00. It arrived on 7/28/09. As of 7/30/09 @ 05:30, how
many hours has that truck been here?

 

[Brownie_D75]

Date Received Time Received Current Date Time
7/28/09 22:30 7/30/09 05:30 =
31 hours

 

[Rick Rothstein]

I understood the headings... I was referring to the column letters and
starting row number. Still assuming we are talking about columns A thru D,
did you try my latest posted formula (after adjusting the row number)?
Assuming the columns as stated and the starting row as 2, that formula is...

=24*((C2+D2)-(A2+B2))

 

[Brownie_D75]

Ok. My columns are as listed:
Date received - Column F, Line 4
Time Received - Column E, Line 4
Current date - Column J, Line 15
05:30 Time - Column K, Line 15

 

[Rick Rothstein]

Then try this formula...

=24*((J15+K15)-(E4+F4))

 

[Brownie_D75]

This is still not working. I don't know what is wrong. Thank you all for your
help. I wil try to figure something out. I have wasted enough of your time.

 

[Rick Rothstein]

Don't worry about wasting my time because you are not... you have a problem
and I would like to help if I can. If you would like to send me a copy of
your workbook so I can look at it directly, that would be okay with me (just
remove the NO.SPAM stuff from my email address).

 

[Rick Rothstein]

Just to keep the thread up to date, the OP emailed me the workbook and the
problem was the OP was using a custom cell format of h:mm on top of the
formula I posted. The final resolution was to use this formula...

=(J$12+K$12)-(E4+F4)

copied down; and this as a Custom Cell Format...

[h]:mm