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:D)/8 This formula works fine for the
available vacation but still gives .50, .25 results
Thanks in advance!

Hi Richard

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

--
Regards

Roger Govier


"Richard" wrote in message
...
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:D)/8 This formula works fine for
the
available vacation but still gives .50, .25 results
Thanks in advance!

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" wrote:

Hi Richard

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

--
Regards

Roger Govier


"Richard" wrote in message
...
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:D)/8 This formula works fine for
the
available vacation but still gives .50, .25 results
Thanks in advance!

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:D)/8)+1)&" Days "&8-MOD(SUM(HList!D:D),8)&" hours
left"

--
Regards

Roger Govier


"Richard" wrote in message
...
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" wrote:

Hi Richard

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

--
Regards

Roger Govier


"Richard" wrote in message
...
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:D)/8 This formula works fine
for
the
available vacation but still gives .50, .25 results
Thanks in advance!

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

"Roger Govier" wrote:

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:D)/8)+1)&" Days "&8-MOD(SUM(HList!D:D),8)&" hours
left"

--
Regards

Roger Govier


"Richard" wrote in message
...
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" wrote:

Hi Richard

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

--
Regards

Roger Govier


"Richard" wrote in message
...
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:D)/8 This formula works fine
for
the
available vacation but still gives .50, .25 results
Thanks in advance!

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.

"Roger Govier" wrote:

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:D)/8)+1)&" Days "&8-MOD(SUM(HList!D:D),8)&" hours
left"

--
Regards

Roger Govier


"Richard" wrote in message
...
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" wrote:

Hi Richard

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

--
Regards

Roger Govier


"Richard" wrote in message
...
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:D)/8 This formula works fine
for
the
available vacation but still gives .50, .25 results
Thanks in advance!

Hello Richard,

try this amendment to Roger's formula

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513975

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

"daddylonglegs" wrote:


Hello Richard,

try this amendment to Roger's formula

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513975

Nice correction, Dayylonglegs. Thank you.

--
Regards

Roger Govier


"daddylonglegs"
wrote in
message
news:daddylonglegs.23fmlm_1140278101.1538@excelfor um-nospam.com...

Hello Richard,

try this amendment to Roger's formula

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=513975