How to calculate hours between times after midnight in Excel?
Supposing you have a time table to record your work time, the time in Column A is the start time of today and time in Column B is the end time of the following day. Normally, if you calculate the time difference between the two times by directly minus "=B2-A2", it will not display the correct result as left screenshot shown. How could you calculate the hours between two times after midnight in Excel correctly?
Add a number of years / months / days / weeks / hours / minutes / seconds to date:
Kutools for Excel’s Formulas utility contains some commonly-used formulas, such as add years to date, calculate age based on birthday and so on. With these usual formulas, you can quickly get the result that you want without remembering any formulas.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
To get the correct calculated result between two times over midnight, you can apply the following formula:
1. Enter this formula: =(B2-A2+(B2<A2))*24 (A2 is the earlier time, B2 is the later time, you can change them as you need) into a blank cell which beside your time data, see screenshot:
2. Then drag the fill handle to the cells that you want to fill this formula, and the time differences between two times after midnight have been calculated at once, see screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoHi, i need help please. I have data that auto loads daily at 5pm. The process is broken into 2 parts (ETL1 and ETL2). ETL1 starts at 5pm till 11pm and ETL2 starts at 11 till 4 am. at 7 am i run a script to check if everything ran. each row has a start and end date time. i would like to flag all data after 5pm yesterday as todays data. Currently when i filter on today, i only see the rows where the date is after 00:00
- To post as a guest, your comment is unpublished.· 11 months agoCan someone explain when the function of addition of the TRUE/FALSE values does so I can understand the formula please?
- To post as a guest, your comment is unpublished.· 1 months agoTrue = 1, False = 0
- To post as a guest, your comment is unpublished.· 1 years agoI think a simple MOD(B2-A2,1) should be enough?
- To post as a guest, your comment is unpublished.· 2 years agoAustin, the formula stated in the article above works well for me
- To post as a guest, your comment is unpublished.· 2 years agoThe formula under bullet point #1 is wrong. =(B2-A2+(B2<A2))*24, but is should read as =B2-A2+(B2<A2)*24.
The parentheses are in the wrong spot.
- To post as a guest, your comment is unpublished.· 1 years agoTHANK YOU! This is definitely the correct formula.
- To post as a guest, your comment is unpublished.· 1 years agoYes Austin! Your formula works, the other formula renders nonsense (in my case). Thanks!!
- To post as a guest, your comment is unpublished.· 1 years agoNo, Austin's correction works for me. The original with the parentheses where they are shown does not work. Maybe a diifferent version of Excel makes a difference? I am on MS Home and Office 2016
- To post as a guest, your comment is unpublished.· 1 years agoThe original formula works better than your suggestion Austin.
I also used =(A2-B2+(A2<B2))*1440
This worked best to convert time into minutes.
- To post as a guest, your comment is unpublished.· 1 years agoDepois de colocar a formatação ao tentar somar a coluna total de horas o valor dá-me errado.
O campo é formatado [h]mm
- To post as a guest, your comment is unpublished.· 11 months ago=(A2-B2+(A2)) sem utilizar a multiplicação por 24