Calculation to Provide Difference Between Two Times Less a Break

Hi All,

I'm new to this forum and this is my first post so hope not to break any rules straight away.

I currently have 3 columns. One is start time, 2nd is finish time and 3rd is break time. I would like to complete the following:

Enter the times in the first two columns as 24 hour time without having to add the colon e.g. instead of 15:45 I would like to type 1545.
Enter the break time (if any) as 0.5 which represents half an hour.
Have a fourth column that calculates the difference between the start and finish time less any break, but have the final result show as hours and mins using a decimal point e.g. 8.75hrs

Example is someone starts at 05:15 and finishes at 14:30 and had a half hour break. Total hours less break is 8.75hrs. I would like to type 0515 column 1, 1430 column 2, 0.5 column 3, with the result being 8.75 column 4.

Is this possible? I am open to alternative solutions. Any help would be greatly appreciated.
Dave

HI ,

try this it is very simple .

Punnam

Hi Punnam,

Thanks for your help. Your solution certainly works however I would like to be able to type the 24hr time in without having to type the colon :

The colons can be quite time consuming to typ when there is alot of data. I don't mind if the colon shows up after but my ultimate aim is not to have to type it in. Is this possible?

hI,

try this Updated

Punnam

Thanks Punnam

Works like a charm. That's the longest formula I have ever seen

Cheers

Ltradey,

Yes this can even shortened, but it will take some time for me
If u r locking for small formula u can wait for more reply. If u r satisfied u can mark the thread as sloved .
u can even thank me by adding some reputation point by using Star Button .
Punnam

Thank you All,

Very helpful indeed. Shorter formula works perfectly.

Cheers
Dave