1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

formula for date & time

Discussion in 'Business Applications' started by t long, Nov 1, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    I need a formula that will calculate the following

    A member goes on a tasking for a complete 24hr period will receive a payment of $16.41. For everday after that he/she is gone and has worked atleast 6 hrs in that day he/she will receive and additional $16.41.

    The payment ($16.41) does not start until the first 24 hour is completed and then every additional day after that (6 hrs or more worked) is another $16.41.

    I have it set up in this way :


    (E18) (F18) (G18) (H18)
    IN DATE IN TIME OUT DATE OUT TIME
    20 SEP 04 08:00 21 SEP 04 14:55

    (I18) TOTAL HOURS - =+IF(H18>0,((G18+H18)-(E18+F18))*24," ")
    (J18) TOTAL DAYS - =+IF(H18>0,FLOOR((G18+H18)-(E18+F18),1)," ")
    (K18) TOTAL ENTITLEMENT - =+IF(J18>0,J18*'FOA Calc'!$C$8,0)

    The problem is that for TOTAL HOURS I am getting an answer of 31 (which is correct) but for total days it should be 2 not 1

    his first 24hr period is his first day and his 7 hrs remaining entitle him to another day.

    can you PLEASE help me with the formula.....

    Thank you

    FORMULA
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi,

    Not sure if this is what you're looking for ...

    Btw, when attempting use of a null value as you're using as your false statement in two of your calculations, it's generally better to not have a space in between your quotes, but rather just leave as a null value (no space, "" , two quotes back to back).


    HTH
     
  3. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    I dont think it's working

    If you take a look at these dates and times I'll explain why :

    date in - 20 sept 04
    time in - 08:00
    date out - 22 sept 04
    time out - 15:00

    so the first 24 hr period is from 08:00 on the 20th to 08:00 on the 21st......this is when the entitlement starts (your first 24 hour completed) if you have completed an entire 24 hr period you are not entitled to the $16.41.

    from 08:00 on the 21st to 08:00 on the 22nd a day in which the member has worked a day over 6 hours (doesn't matter if he worked 24, as long as every day after the initial 24 hour period is equal to or more that 6 hrs)

    The remaining time 7 additional hrs (08:00 on the 22nd until 15:00 on the 22nd)......since this is more than 6hrs he/she will receive an additional days entitlement.

    I plugged all of this data into the cells using the formula that you provided me with - if I make the end time less than 6 hrs remaining instead of 7 as I explained above the entitlement should only be for 2 days not 3 days. Can you help me get this formula to work?

    thanks
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well then maybe you could just shorten it to ...

     
  5. t long

    t long Thread Starter

    Joined:
    Jan 19, 2004
    Messages:
    52
    sorry.....

    it's still not calculating properly

    It's not calculating the remaining hours ( 6hrs or more )

    this is gonna drive me crazy.....there's got to be a formula that works!
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Which cell are you referring to? I'm confused. Are you talking about finding days or hours? If you add an "=" sign directly after the first greater than sign ( > ), this will also take care of those equaling 24 hours (hind sight on my part). The calculation I just provided does what you're asking (I thought) for days, not hours. Can you post an example spreadsheet, and provide at least 3 examples along with your expected results?
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/291195

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice