simple math equation

[Richard]

If you're working in hours, say 8 hour increments. How would you write the
formula to reflect every 2 hours instead it's displaying halves and
quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs or .6hrs)
Example: If I've schelduled or taken 42 hours vacation and I have 20 days
available. I'd like the results to display ( 5 days and 2 hrs) used. (14 days
and 6 hrs available) This formula works fine for the scheduled vacation but
gives .50, .25 results =SUM(HList!D)/8 This formula works fine for the
available vacation but still gives .50, .25 results
Thanks in advance!

 

[Roger Govier]

Hi Richard

Try
=INT(SUM(HList!D)/8)&"Days "&MOD(SUM(HList!D),8)&" hours"

 

[Richard]

Thank you so much! It works perfect for the Scheduled or used vacation but
the Available vacation is getting a #VALUE! probably due to the text? Thanks
so much. It's still perfect!

 

[Roger Govier]

Hi Richard

Didn't notice the second part of your request.
Either use 20 for the number of days total holiday available at the
beginning of the formula, or put 20 in a cell (A1 in this example)

=A1-(INT(SUM(HList!D)/8)+1)&" Days "&8-MOD(SUM(HList!D),8)&" hours
left"

 

[Richard]

Once again It's perfect! Thanks so very much!!!

Hi Richard

Didn't notice the second part of your request.
Either use 20 for the number of days total holiday available at the
beginning of the formula, or put 20 in a cell (A1 in this example)

=A1-(INT(SUM(HList!D)/8)+1)&" Days "&8-MOD(SUM(HList!D),8)&" hours
left"

 

[Richard]

I'm almost embarrased to ask for help again. But... on the last formula the
hours doesn't change to 0. It shows the correct Days but instead of 0 it
shows 8 for the hours. The first formula works perfect.

 

[daddylonglegs]

Hello Richard,

try this amendment to Roger's formula

=A1-CEILING(SUM(Hlist!D)/8,1)&" Days "&MOD(8-SUM(Hlist!D),8)&"
hours left"

 

[Richard]

Yes!!! That did the trick, you guys are amaging! Thank you!

 

[Roger Govier]

Nice correction, Dayylonglegs. Thank you.

--
Regards

Roger Govier


"daddylonglegs"
message
news:[email protected]...