# formula for date & time

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

Not open for further replies.
1. ### t longThread 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.

Thank you

FORMULA

2. ### 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 longThread 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

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

5. ### t longThread 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

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?

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.

over 733,556 other people just like you!