Calculating Billable Hours

[lzweifel]

Can I create a formula to calculate hours overlapping from one day to the next without entering the date. For example...

7:30 pm - 1:30 pm (start work at 7:30 pm and return next day at 1:30 pm) total hours 18 hours.

I know it works if I put in 03-18-10 7:30 pm and 03-19-10 1:30 pm, but I don't really want the date in this column.

Thanks!

 

[taigovinda]

Is this what you are looking for?

Excel Workbook
	A	B	C
1	8:00AM	4:00AM	20.00
2	2:00PM	6:00PM	4.00
3	12:00AM	6:00PM	18.00
4	3:00AM	8:00PM	17.00
5	8:00PM	6:00PM	22.00
6	3:00PM	7:00PM	4.00
7	6:00AM	5:00AM	23.00
8	7:00PM	9:00AM	14.00
9	6:00PM	10:00AM	16.00
10	8:00PM	4:00AM	8.00
Sheet1

Cell Formulas
Range	Formula
C1	=(B1-A1)*24+(B1)*24

...or you could enter the date, but use cell formatting to only show the time. Then the formula would just be subtraction.

 

[6StringJazzer]

If start time is in A1 and end time is in B1, then the elapsed time is

=IF(B1>A1,B1-A1,B1+1-A1)

This fails if the elapsed time is >= 24 hours. If for example you work from 8:00 AM one day to 9:00 the next day for 25 hours, then this formula would think you only worked one hour, the same day. There is no way around this if you are not including the date.

So don't work too hard

 

[6StringJazzer]

On second review, my approach will give you the result in hh:mm:ss format. For billing purposes you probably want fractional hours, in which case taigovinda's approach is more appropriate.

 

[lzweifel]

Hi there... thanks for your help.

Taigovinda's formula will work perfect - so I do not have to enter the date just the times?

Also, what if I have time out, time in and then straight hours to add or subtract... for example...

8 pm - 7 am... subtract 2 hours for down time ... Add 3 hours for Shop Time?

 

[Canapone]

Ciao,

=B1-A1+(A1>B1)

could be another solution.

Format cells as [h]:mm.

If you want to subtract 1 hour (D1)

=B1-A1+(A1>B1)-D1/24

or
=B1-A1+(A1>B1)-1/24

And so on.

I hope it helps

 

[taigovinda]

Hi there... thanks for your help.

Taigovinda's formula will work perfect - so I do not have to enter the date just the times?

Also, what if I have time out, time in and then straight hours to add or subtract... for example...

8 pm - 7 am... subtract 2 hours for down time ... Add 3 hours for Shop Time?

I think you would probably want to insert another column or two for that. Then type in the number of hours to subtract in those extra columns - and in your formula, modify it so that it does the same thing as now, and then just subtracts the contents of those extra columns (e.g., if the extra columns are C and D, then in E1: =(B1-A1)*24+(B1<A1)*24-C1-D1). Hope that makes sense.

 

[scottylad2]

This would allow you to have a break soewhere. You would adjust according to how many breaks you needed

Excel Workbook
	I	J	K	L	M	N	O
3	start	stop	Hours	start	stop	Hours	Total
4	08:00	10:00	02:00	12:00	11:45	23:45	25.75
Sheet1

Excel Workbook
	M
4	11:45
Sheet1

Excel Workbook
	O
4	25.75
Sheet1

 

[lzweifel]

Thank you so very much.... this is exactly what I needed - to be able to stop and start!!!!

Mr. Excel is the BEST!!