• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating time between two dates.

fredieusa

fredieusa

New Member
Hello All.

The date is ONLY listed once cell A8.
Start time on October 27 is listed in B9 and end time on C9.
The end time is naturally on the NEXT day.

Cell H4 should show a total of 9 Hrs. How do I put in the formula. I would like to try WITHOUT having to enter the end date please. I have attached the file here.

The cell H4 should calculate total time for all the time differences entered in B and C range.

Thanks for your help.
 

Attachments

fredieusa said:
The cell H4 should calculate total time for all the time differences entered in B and C range.
Click to expand...
In H4, array-enter (commit to sheet using Ctrl+Shift+Enter, not just Enter) the following:
=SUM(IF(C9:C72 >= B9:B72, C9:C72 - B9:B72, C9:C72 + 1 - B9:B72))
This will give you the number of days, so to see hours, especially if they're likely to exceed 24, format the cell [h]:mm
However, if you want the value as the number of hours for arithmetic (if say people are paid by the hour), multiply the result by 24 and don't have any special cell formatting.

ps. I suspect Narayan's =IF(C9 > B9, C9 - B9, C9 + 1 - B9) should be tweaked to =IF(C9 >= B9, C9 - B9, C9 + 1 - B9)
 
Last edited:
wow, you gentlemen (again, i am assuming that you are men, forgive me if you are not) make it seem so simple and here I was scratching my head at 2AM. Much thanks.
 
You must log in or register to reply here.
Back
Top