Results 1 to 5 of 5
  1. #1
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts

    More Problems With Time

    This is one of my formula's: =IF(B12=2;F12;IF(AND(B12=1;F12<>"");IF(F12<0.25;1; F12-0.25);""))

    As you can see if F12 is greater than 0.25 then its F12-0.25 which basically is if I work more than 6 hours then its overtime. My hours are going to change soon to 7 hours a night as standard so is there an easier way to write the formula than figure out what 7 hours is as a decimal?

    Thanks in advance for any suggestions.

    Alan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Excel uses days as the unit of time. So you can use 7/24 for the hours in terms of day (the decimal time). [The 0.25 you were using is = 6/24]

    Steve

  3. #3
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by sdckapr View Post
    Excel uses days as the unit of time. So you can use 7/24 for the hours in terms of day (the decimal time). [The 0.25 you were using is = 6/24]

    Steve
    I knew that Steve, just wondered if there was another way to get the same result. I mean the decimal of 7/24 is 0.29166666666. I guess I will have to change the formulas to use that.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That is the decimal, but the formulas can accept the formula which may make it more clear...

    =IF(B12=2;F12;IF(AND(B12=1;F12<>"");IF(F12<7/24;1; F12-7/24);""))

    You could even store the hours (7) in a cell and use that cell reference in the formula, so you only have to change the hours in one place in the workbook...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    AlanWade (2014-05-18)

  6. #5
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Thanks Steve! It never crossed my mind to use a cell reference with the hours in it.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •