calculating days + holidays

G

gambit

Hi all

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

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

Any help would be most welcomed.

-
 
O

oldchippy

gambit said:
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
Click to expand...
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 :)
 
G

gambit

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

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

oldchippy

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

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

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

=B1-A1
B1 end date
A1 start date

oldchippy :
 
B

Bob Phillips

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)
 
D

daddylonglegs

Perhaps easier to use

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