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; (30662.50, « »)
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.itags.org/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
- 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.itags.org.discussions.microsoft.com> wrote in message
news:1E071215-9636-490A-995D-E208F793AA9B.excel.itags.org.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
- 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.itags.org.discussions.microsoft.com> wrote in message
news:1E071215-9636-490A-995D-E208F793AA9B.excel.itags.org.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