Auto Invoice Help

Hello,

It has been years since using excel to its full advantage not since I was at school.

I would like to create an invoice that auto completes when given a start & finish time, and was wondering if anyone can help?

So what I am trying to achieve;

I have 7 Fields, These are required by the company I work for
Date(A), Start(B), Finish(C), Reference 1, Reference 2, Total Hours(F), Amount Claimed(G)

I would like to be able to put the start time and finish time in and it does the rest for me, with a few conditions;

I need the formula to work out the following;

Deference of (B & C) - 30 mins (For my break) and present it as a decimal to a whole number, so 11:30 would equal 11.5.

The rest of the spread sheet is easy its just that formula I am having trouble working out due to an issue i can't explain;

So I added a couple of extra cells to work out what I needed

(H) =

=IF(B20="",0,IF(B20<C20,B20+1,B20)-C20-I20)

(I) =

00:30 (This is my automatic break deduction)

and

(F) =

=H20*24

If I enter 06:00 & 18:00 in relevant cells, I get the correct output in (F) '11.5'

but if I finish just a little later so the times are 06:00 & 18:07 I get an incorrect value '11.38' which is less.

I understand the issue its less time between the the 2 number when calculated but in reality its more time, but I don't know how to get the correct answer that I need which in this case should be '11.62'

I also need the calculation to work if I work a night shift so the times are switched, the current formula works if its a round number.

Can anyone help with this, I have attached what I have created so far to look at if it doesn't make sense.


Thank you in advance,

Regards Larkum

Try this formula

Formula:

=IF(B20="","",C20-B20-I20+(B20>C20))

Thank you all for your help, massive help