### Tags: calculate, excel, gt8, half, hrs, microsoft, min, msdn, sheet, software, spread, subtract, time

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

On Microsoft » Microsoft Excel

2,328 words with 3 Comments; publish: Fri, 23 May 2008 02:33:00 GMT; (30660.55, « »)

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.

*http://excel.todaysummary.com/q_microsoft-excel_234007.html*

All Comments

Leave a comment...

- 3 Comments
- 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.

#1; Fri, 23 May 2008 02:34:00 GMT

- I did a timesheet in excel several years back and found the formula got
- 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

"lucy" <lucy.excel.todaysummary.com.discussions.microsoft.com> wrote in message

news:1E071215-9636-490A-995D-E208F793AA9B.excel.todaysummary.com.microsoft.com...

>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.

#2; Fri, 23 May 2008 02:35:00 GMT

- If the start time is in A6 and the finish time is in B6:
- 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)

"lucy" <lucy.excel.todaysummary.com.discussions.microsoft.com> wrote in message

news:1E071215-9636-490A-995D-E208F793AA9B.excel.todaysummary.com.microsoft.com...

> 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.

#3; Fri, 23 May 2008 02:36:00 GMT

- Keeping it as time your formula would be