How do I compute number of hours using date and hours cell

[razon98]

Hi All, I have two sets of data start date with hours and end date with hours. How do I compute using these data?

Column 1: Start Date - 27-3-2023
Column 2: Start time - 13:00
Column 3: End Date - 28-3-2023
Column 4: End time - 14:00
Column 5: Duration - 25 hours

Thank you.

 

[James006]

Hi,
With all your data in row 2, for cell E2:

=C2+D2-(A2+B2)

make sure to format E2 with custom format: [h]:mm

 

[razon98]

Hi,
With all your data in row 2, for cell E2:

=C2+D2-(A2+B2)

make sure to format E2 with custom format: [h]:mm

Hi, thank you but it failed to capture correctly if the date is few days away.

 

[James006]

What do you mean by " a date a few days away " ...

Would you mind posting an example ...

 

[razon98]

What do you mean by " a date a few days away " ...

Would you mind posting an example ...

please refer to screenshot, thank you so much

 

[James006]

Just checked cell E2 formula ... with custom format [h]:mm or 49:00

By the way, even with the wrong format, the result is 2.0416 and not 1.00

So you should make sure dates are dates ...and times are times ...

 

[Siddharth Rout]

please refer to screenshot, thank you so much

That is because, you did not format the cell as recommended by james.

make sure to format E2 with custom format: [h]:mm

You have formatted the cell as hh:mm. You have to use the format [h]:mm

 

[James006]

As explained very clearly by Barry Houdini :

1. h:mm is designed for "clock times"...so it never shows more than 23:59....

2. for "elapsed times" (where cumulative hours might be over 24+) you must use [h]:mm