|
days in month - Ajit
If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of
days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power |
Hi!
Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . |
Here is a way that doesn't need both dates, and A1 can hold any date in the
month =4+(DAY($A$1-DAY($A$1)+35)<WEEKDAY($A$1-DAY($A$1)-C1)) where C1 holds the day number (1 =Mon, 2=Tue), etc. -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . |
Sorry Biff, Its not working! Its giving #Name error.
"Biff" wrote: Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . |
"Ajit Munj" wrote
Sorry Biff, Its not working! Its giving #Name error. Think you probably missed out the significance of Biff's last line <g Where X = 1 through 7 for Monday through Sunday Plug a number (1 to 7) into the function to replace the "X", for e.g.: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=1)) which'll return 4 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
This has been bothering me :-). Having to enter two dates, and the first and
last date of the month is not user friendly IMO (users make mistakes, lots of them). This can probably be done better, but here is an alternative on Biff's formula that addresses both issues =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1-DAY($A$1)+1&":"&DATE(YEAR($A$1),MON TH($A$1)+1,0))),2)=X)) X is as defined by Biff -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . |
On Wed, 9 Mar 2005 23:01:03 -0800, Ajit Munj wrote:
If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit =4+(DAY(A1-DAY(A1)+1-WEEKDAY(A1-DAY(A1+desired_week_day))+35)7) Assumes your date is in A1. For desired_week_day substitute 1 for Sunday 2 for Monday etc. or a cell reference with those numbers in it. --ron |
Hi Bob!
I've seen this formula before. I'm sure it works but I haven't tried it out yet. But just looking at it I would like to know the logic behind 4+ and +35. Biff -----Original Message----- Here is a way that doesn't need both dates, and A1 can hold any date in the month =4+(DAY($A$1-DAY($A$1)+35)<WEEKDAY($A$1-DAY($A$1)-C1)) where C1 holds the day number (1 =Mon, 2=Tue), etc. -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . . |
Hi Bob!
Versatility! It doesn't have to be the first and last date of any month! It could be any two dates. Biff -----Original Message----- This has been bothering me :-). Having to enter two dates, and the first and last date of the month is not user friendly IMO (users make mistakes, lots of them). This can probably be done better, but here is an alternative on Biff's formula that addresses both issues =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1-DAY($A$1) +1&":"&DATE(YEAR($A$1),MON TH($A$1)+1,0))),2)=X)) X is as defined by Biff -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . . |
Hi Biff,
When I tried your original formula, it counted the number of a particular day between those dates, whereas I read the OP to say the number in the month. Subtle but different by my reading. Regards Bob -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi Bob! Versatility! It doesn't have to be the first and last date of any month! It could be any two dates. Biff -----Original Message----- This has been bothering me :-). Having to enter two dates, and the first and last date of the month is not user friendly IMO (users make mistakes, lots of them). This can probably be done better, but here is an alternative on Biff's formula that addresses both issues =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1-DAY($A$1) +1&":"&DATE(YEAR($A$1),MON TH($A$1)+1,0))),2)=X)) X is as defined by Biff -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . . |
Hi Biff,
The 4 is the base point, every month will have at least 4 instances of each day in that month. The 35 is used (5 weeks x 7 days) to get outermost 5 week date from teh start of the month, which is then compared to the weekday of the last day of the previous month less the day number being checked. If this is True, it has the effect of adding 1 to the base point, that is identifying if there are 5. -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi Bob! I've seen this formula before. I'm sure it works but I haven't tried it out yet. But just looking at it I would like to know the logic behind 4+ and +35. Biff -----Original Message----- Here is a way that doesn't need both dates, and A1 can hold any date in the month =4+(DAY($A$1-DAY($A$1)+35)<WEEKDAY($A$1-DAY($A$1)-C1)) where C1 holds the day number (1 =Mon, 2=Tue), etc. -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)=X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . . |
Got it! Thanks Bob.
Biff -----Original Message----- Hi Biff, The 4 is the base point, every month will have at least 4 instances of each day in that month. The 35 is used (5 weeks x 7 days) to get outermost 5 week date from teh start of the month, which is then compared to the weekday of the last day of the previous month less the day number being checked. If this is True, it has the effect of adding 1 to the base point, that is identifying if there are 5. -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi Bob! I've seen this formula before. I'm sure it works but I haven't tried it out yet. But just looking at it I would like to know the logic behind 4+ and +35. Biff -----Original Message----- Here is a way that doesn't need both dates, and A1 can hold any date in the month =4+(DAY($A$1-DAY($A$1)+35)<WEEKDAY($A$1-DAY($A$1)-C1)) where C1 holds the day number (1 =Mon, 2=Tue), etc. -- HTH RP (remove nothere from the email address if mailing direct) "Biff" wrote in message ... Hi! Here's one way: A1 = 2/1/2005 A2 = 2/28/2005 =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2) =X)) Where X = 1 through 7 for Monday through Sunday Biff -----Original Message----- If I put 01.02.2005 in Cell for Feb, 2005, How can I calculate total no. of days i.e. total Sundays, total Mondays, total Tuesdays etc. in that month? Ajit -- Knowldege is Power . . . |
| All times are GMT +1. The time now is 05:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com