An xcel question about handling time..

In Excel I am formulating a time sheet for some payroll calculations...

I am fine with totals and the calculation of time ---

I am struggling with the format of a simple calculation.

C3 = 9 pm for example
B3 = 1 pm for example

This is an 8 hour shift --- I can get the sheets to do this no problem...
BUT ... I am having a problem with the automatic break calculation
here we deduct 30 minutes for lunch/break for any shift over 6 hours
I believe I have some syntax problem to do this calculation... help plz

=IF((C3-B3)>(6 hours),then (C3-B3)-:30,else (C3-B3))

What should be the (6 hours) in syntax? I have tried 6, 6:00....
I will need the same information about deducting :30 minutes for the final calculation...

So, the overview is this...
1) Calculate the difference between two times

2) Calculate if it is over 6 hours

3) if over 6 hours, then deduct :30 minutes from that total

4) else - shift must be 6 hours or less, and the total need not be adjusted.

the normal math calculation is simple...

=IF((C3-B3)>6,(C3-B3-.5),C3-B3)

I am struggling a bit with time calculations...

Your help is greatly appreciated...

 

I think I may have figured it out....

=IF((C6-B6)>0.25,((C6-B6)-0.020833),C6-B6)

Using the decimal eq for 6 hours (0.25)

The decimal eq for 30 minutes (0.020833)

This seems to work, is there a better, or more simple way?

 

Hi-ya... Are you sure you've got the right post?

 

Re: An xcel question about handling time

Trying to subtract time from time and get a difference in hours

Total elapsed time example 50:30

Trying to subtract a number of hours from this to get a result in hours difference


For example 50:30 - 48:00 should equal 2:30

any thoughts ?

 

what is wrong with this formula? Actually, Excel 2007 will display the .5 in the formula as 0.5. Anyway, it seems to yield a valid answer with the partial hour being displayed as a decimal. Is that a problem?

 

Yes, the user would prefer to generate everything in time format...
They will be using it for a schedule - They want the employees to see
9:30 AM as the time to start not 9.50, they are certain that people will
be showing up for work at 9:50 am instead of 9:30

 

When you subtract the contents of one cell from another to find the amount of time elapsed between them, the result is a serial number that represents the elapsed hours, minutes, and seconds. To make this number easier to read, use the h:mm time format in the cell that contains the result.
if cells C2 and D2 contain the formula =B2-A2, and cell C2 is formatted in the General format, the cell displays a decimal number (in this case, 0.53125, the serial number representation of 12 hours and 45 minutes).
A1: Start Time
B1: End Time
C1: Difference
D1: Difference (General) (h:mm)

A2: 6:30 AM
B2: 7:15 PM
C2: 0.53125
D2: 12:45

To convert a decimal number (0.00) to its serial date equivalent (h:mm:ss), you must convert the serial number to a decimal by converting to a 24-hour base. You do this by dividing the time by 24 as follows

=Time/24

where Time is the number that you want to convert from a decimal number to a date serial number and can be a cell reference or a real number. For example, if you have a value of 4.5 to represent four hours and 30 minutes in cell A1, the formula is:

=A1/24

The result is 4:30