How do i subtract a half hour from a time when >8 hrs.

[Guest]

I want a spread sheet that will calculate the hours and min between two times
and when the hours worked are greater than 8 i want to subtract a half hour.
I have =IF(((INT(B6-A6)*24)).*),(INT((B6-A6*24)-0.5),(INT((B6-A6*24)))
when B6 is the finish time and A6 is the start time
the formula i have now only shows me the hours worked and i need the exact
hours and min.

 

[Mike Brown]

I did a timesheet in excel several years back and found the formula got
very messy to display hours and minutes since the computer thinks in
decimal and minutes are sexidecimal. I ended up needing an extra cell
to break up the hours and minutes (multiplying the partial hour by 60
to convert it to minutes).

I doubt I still have the template anywhere but I'll take a look.

 

[Trevor Shuttleworth]

If the start time is in A6 and the finish time is in B6:

=(B6-A6)-IF(B6-A6>1/3,1/48,0)

Format as [hh]:mm

Regards

Trevor

 

[Bob Phillips]

Keeping it as time your formula would be

=IF((B6-A6)>TIME(8,0,0),B6-A6-TIME(0,30,0),B6-A6)

or I would use

=B6-A6-(B6-A6>TIME(8,0,0))*TIME(0,30,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)