24 hr time calculations without semi colons

**nje** · 08-08-2011, 10:57 AM

It's a long time since I have used excel and I have after a long illness just got back to an old project. Unfortunately, I have forgotten a lot that I had previously learnt from this forum despite re-reading my previous posts
I have attached a sample created in excel 2010.
Row 5 with times entered with the semi colons works just as needed and the calculations in H,J,K,L work OK.
but the full workbook will need lots of entries so I want to do away with having to put in the time consuming semi colons.
In row 6,I have entered the times without the semi colons and formatted the cells as 00\:00.
What my question is, Does anyone know how can I get columns H J K L to do the calculations? in row 6
Thank You

**DonkeyOte** · 08-08-2011, 11:57 AM

As a starter:

F6:
=MOD(TEXT(D6,"00\:00")-TEXT(C6,"00\:00"),1)

From the above you can see how you can take the non-time values and use in time context ... the MOD is an alternative way of handling times that cross midnight (assumes duration never exceeds 24 hours)

**nje** · 08-08-2011, 01:03 PM

DonkeyOte
Thank you for your quick response and your solution, that seems to do the trick but it has left me with another problem, which I did learn how to solve, but it has got a bit too complicated for me this time. I hope you can help with it. I have posted the sample again.
The calculations now work out nicely thanks, but when I drag the formula down each cell fills with either zeros or the calculation from above, What I want are for the cells to remain blank, but I can't follow my own notes on how to sort it.
Thanks

**DonkeyOte** · 08-08-2011, 01:38 PM

As an example

F6:
=IF(COUNT($C6:$D6)<2,"",MOD(TEXT($D6,"00\:00")-TEXT($C6,"00\:00"),1))

**nje** · 08-08-2011, 01:50 PM

Congratulations, you did it.
Thank you. I don't quite understand the formula, but will try to decipher it.
Thanks again for your time and expertise.
I did want to mark this post as 'solved' but I can't remember how to do it