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; (306109.38, « »)

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.

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