| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| problem of calculating years months and days | Excel Discussion (Misc queries) | |||
| Calculating Number of days from one date to another | Excel Discussion (Misc queries) | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
| Calculating Production/Man Days in Excel | Excel Discussion (Misc queries) | |||
| Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions | |||
Linear Mode
