calculating days + holidays
 

Hi all

I need to work out the days left till the end of a certain date from
today and then add any holidays to that count if there are any between
now and then. My result shout be a number.

I live in South Africa so excel does not have the holidays here
entered, so I will need a way of adding these somehow.

Any help would be most welcomed.

-G


--
gambit
------------------------------------------------------------------------
gambit's Profile: http://www.excelforum.com/member.php...o&userid=37867
View this thread: http://www.excelforum.com/showthread...hreadid=574137

calculating days + holidays
 

gambit Wrote:
Hi all

I need to work out the days left till the end of a certain date from
today and then add any holidays to that count if there are any between
now and then. My result shout be a number.

I live in South Africa so excel does not have the holidays here
entered, so I will need a way of adding these somehow.

Any help would be most welcomed.

-G
Hi Gambit,

Try the formula NETWORKDAYS, it returns the number of whole working
days between start date and end date. Working days exclude weekends and
any dates identified in holidays.

If this function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.

oldchippy :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=574137

calculating days + holidays
 

Thanks for the help but I kinda need the opposite, I need to add the
holidays to the day count as well as weekends.

I'm calculating an interest payment that is settled at the end of the
month but holidays delay settlement and so the interst to be payed
increases.


--
gambit
------------------------------------------------------------------------
gambit's Profile: http://www.excelforum.com/member.php...o&userid=37867
View this thread: http://www.excelforum.com/showthread...hreadid=574137

calculating days + holidays
 

gambit Wrote:
Thanks for the help but I kinda need the opposite, I need to add the
holidays to the day count as well as weekends.

I'm calculating an interest payment that is settled at the end of the
month but holidays delay settlement and so the interst to be payed
increases.
Hi gambit,

If you need to add holidays and weekend, why not just take the end date
from the start date.

=B1-A1
B1 end date
A1 start date

oldchippy :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=574137

calculating days + holidays
 

Do you mean that a holiday counts twice?

If so then perhaps like this

=B1-TODAY()+SUMPRODUCT(--ISNUMBER(MATCH(M1:M10,ROW(INDIRECT(TODAY()&":"&B1) )
)))

where B1 is the end date, and M1:M10 is a list of your holiday dates

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"gambit" wrote in
message ...

Thanks for the help but I kinda need the opposite, I need to add the
holidays to the day count as well as weekends.

I'm calculating an interest payment that is settled at the end of the
month but holidays delay settlement and so the interst to be payed
increases.


--
gambit
------------------------------------------------------------------------
gambit's Profile:
http://www.excelforum.com/member.php...o&userid=37867
View this thread: http://www.excelforum.com/showthread...hreadid=574137

calculating days + holidays
 

Perhaps easier to use

=B1-TODAY()+SUMPRODUCT(--(M1:M10=TODAY()),--(M1:M10<=B1))


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

calculating days + holidays
 

Indeed it is, and without an INDIRECT as an added bonus.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"daddylonglegs"
wrote in message
news:daddylonglegs.2cytxj_1156290612.9001@excelfor um-nospam.com...

Perhaps easier to use

=B1-TODAY()+SUMPRODUCT(--(M1:M10=TODAY()),--(M1:M10<=B1))


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