Remaining off days in a month
- Thread starter dsudhakar
- Start date
shrivallabha
Excel Ninja
Hi dsudhakar,
Welcome to Chando
rg.
Do you want to include 10 Nov 2012 or exclude in calculation? Following formula excludes 10 Nov 2012 but includes 30 Nov 2012 which is end date in this month.
In A1: put the date of your choice.
In any other cell place this formula:
=SUMPRODUCT(--(WEEKDAY(A1+ROW($A$1:INDEX(A:A,EOMONTH(A1,0)-A1)))={1}))
the formula can be extended for any day with minor tweak.
Welcome to Chando
rg.Do you want to include 10 Nov 2012 or exclude in calculation? Following formula excludes 10 Nov 2012 but includes 30 Nov 2012 which is end date in this month.
In A1: put the date of your choice.
In any other cell place this formula:
=SUMPRODUCT(--(WEEKDAY(A1+ROW($A$1:INDEX(A:A,EOMONTH(A1,0)-A1)))={1}))
the formula can be extended for any day with minor tweak.
NARAYANK991
Excel Ninja
Hi Sudhakar ,
Try this :
=SUM(IF(WEEKDAY(ROW(INDIRECT(Start Date&":"&End Date)))=1,1,0))
which is to be entered as an array formula , using CTRL SHIFT ENTER.
Start Date is a reference to your cell which has the start date , say A1. End Date is a reference to the cell which has the end date , say A2.
Sunday has a day number of 1 , hence the check for equality to 1. If you want to check for the number of Saturdays , the number should be changed to 7 , as follows :
=SUM(IF(WEEKDAY(ROW(INDIRECT(Start Date&":"&End Date)))=7,1,0))
If you want the End Date always to be the end of the month , you can replace the End Date in the above formula , by the function EOMONTH(Start Date,0) ; to use this function , you will have to have the Analysis Toolpak add-in installed.
Narayan
Try this :
=SUM(IF(WEEKDAY(ROW(INDIRECT(Start Date&":"&End Date)))=1,1,0))
which is to be entered as an array formula , using CTRL SHIFT ENTER.
Start Date is a reference to your cell which has the start date , say A1. End Date is a reference to the cell which has the end date , say A2.
Sunday has a day number of 1 , hence the check for equality to 1. If you want to check for the number of Saturdays , the number should be changed to 7 , as follows :
=SUM(IF(WEEKDAY(ROW(INDIRECT(Start Date&":"&End Date)))=7,1,0))
If you want the End Date always to be the end of the month , you can replace the End Date in the above formula , by the function EOMONTH(Start Date,0) ; to use this function , you will have to have the Analysis Toolpak add-in installed.
Narayan