date to time formula

  • Thread starter Thread starter Brownie_D75
  • Start date Start date
B

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?
 
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.
 
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?
 
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
 
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.

Jacob Skaria said:
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


Brownie_D75 said:
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?
Click to expand...
Click to expand...
 
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


Brownie_D75 said:
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.

Jacob Skaria said:
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


Brownie_D75 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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))
 
Yes I did & it still calculates as 4 hours. Where did the other 29 hours go?

Jacob Skaria said:
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


Brownie_D75 said:
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.

Jacob Skaria said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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


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

Jacob Skaria said:
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


Brownie_D75 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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))
 
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
 
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.
 
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).
 
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
 
Back
Top