| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to find each day prior to first friday, monthly
I am trying to create a calendar with a "grace period" that ends on the first
friday of each month. I can identify the first friday: =AND(WEEKDAY(A1)=6,DAY(A1)<=7) and the first week: =AND(DAY(A1)<=7) but can't get my mind around selecting all the days of the month prior to the first friday... Thanks, Paul |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
I'm not sure what mean by "selecting all the days of the month prior to the
first Friday." But for a given date in A1, you can calculate the first Friday of the month as =A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),5,4,3,2,1,7,6). The approach in that formula is to add an appropriate number of days to the last day of the prior month. HTH. --Bruce "PaulRMcHanJr" wrote: I am trying to create a calendar with a "grace period" that ends on the first friday of each month. I can identify the first friday: =AND(WEEKDAY(A1)=6,DAY(A1)<=7) and the first week: =AND(DAY(A1)<=7) but can't get my mind around selecting all the days of the month prior to the first friday... Thanks, Paul |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
Hi Paul,
To find the 1st Friday of next month, try: =INT(DATE(YEAR(A1),MONTH(A1)+1,1)/7+0)*7+6 Change the ‘+1’ to get another month, the ‘+0’ to get another week & the ‘+6’ to get another day. To find out whether the date in A1 is within this month's grace period, try: =A1<=INT(DATE(YEAR(A1),MONTH(A1)+0,1)/7+0)*7+6 Cheers -- macropod [MVP - Microsoft Word] "PaulRMcHanJr" wrote in message ... | I am trying to create a calendar with a "grace period" that ends on the first | friday of each month. | I can identify the first friday: | =AND(WEEKDAY(A1)=6,DAY(A1)<=7) | and the first week: | =AND(DAY(A1)<=7) | but can't get my mind around selecting all the days of the month prior to | the first friday... | Thanks, | Paul |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
The line you typed, returned dates up to the first Wednesday. I changed the
"+6" to "+8" and it works perfectly, but I have not been able to understand "how?" I would like an explaination of the solution, if it's not too much trouble. I don't understand the redundancies (i.e. (X/7)*7) or the "+0", but the formula doesn't work without them. Also, how does using "1" for the DAY affect the results? The "YEAR", "MONTH", and "DAY=1" are the same for all the cells in the month, but the results change. Confounded, but happy... -- Thanks "macropod" wrote: Hi Paul, To find the 1st Friday of next month, try: =INT(DATE(YEAR(A1),MONTH(A1)+1,1)/7+0)*7+6 Change the €˜+1 to get another month, the €˜+0 to get another week & the €˜+6 to get another day. To find out whether the date in A1 is within this month's grace period, try: =A1<=INT(DATE(YEAR(A1),MONTH(A1)+0,1)/7+0)*7+6 Cheers -- macropod [MVP - Microsoft Word] "PaulRMcHanJr" wrote in message ... | I am trying to create a calendar with a "grace period" that ends on the first | friday of each month. | I can identify the first friday: | =AND(WEEKDAY(A1)=6,DAY(A1)<=7) | and the first week: | =AND(DAY(A1)<=7) | but can't get my mind around selecting all the days of the month prior to | the first friday... | Thanks, | Paul |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
Hi Paul,
| The line you typed, returned dates up to the first Wednesday. Are you sure you've got the cell references correct? Unless you're using the 1904 date system, the formula =A1<=INT(DATE(YEAR(A1),MONTH(A1)+0,1)/7+0)*7+6 will return 'TRUE' for all dates up to and including the 1st Friday. If you're using the 1904 date system, the formula still returns 'TRUE' for all dates up to and including the 1st Thursday. | I don't understand the redundancies (i.e. (X/7)*7) or the "+0" The INT(date/7)*7 tells Excel to take the first day of the month, then divide by 7, throw away the remainder, then multiply by 7 again. This finds the most recent Saturday on or before the date being tested. This is NOT a redundancy - you need it so that you can calculate the following Friday. The '+0' bits can be deleted - I simply left them there in case you needed to vary either the day or the month. Cheers -- macropod [MVP - Microsoft Word] "PaulRMcHanJr" wrote in message ... | The line you typed, returned dates up to the first Wednesday. I changed the | "+6" to "+8" and it works perfectly, but I have not been able to understand | "how?" I would like an explaination of the solution, if it's not too much | trouble. I don't understand the redundancies (i.e. (X/7)*7) or the "+0", but | the formula doesn't work without them. Also, how does using "1" for the DAY | affect the results? The "YEAR", "MONTH", and "DAY=1" are the same for all | the cells in the month, but the results change. | Confounded, but happy... | -- | Thanks | | | "macropod" wrote: | | Hi Paul, | | To find the 1st Friday of next month, try: | =INT(DATE(YEAR(A1),MONTH(A1)+1,1)/7+0)*7+6 | Change the '+1' to get another month, the '+0' to get another week & the '+6' to get another day. | | To find out whether the date in A1 is within this month's grace period, try: | =A1<=INT(DATE(YEAR(A1),MONTH(A1)+0,1)/7+0)*7+6 | | Cheers | | -- | macropod | [MVP - Microsoft Word] | | | "PaulRMcHanJr" wrote in message ... | | I am trying to create a calendar with a "grace period" that ends on the first | | friday of each month. | | I can identify the first friday: | | =AND(WEEKDAY(A1)=6,DAY(A1)<=7) | | and the first week: | | =AND(DAY(A1)<=7) | | but can't get my mind around selecting all the days of the month prior to | | the first friday... | | Thanks, | | Paul | | | |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
On Wed, 13 Dec 2006 16:51:00 -0800, PaulRMcHanJr
wrote: I am trying to create a calendar with a "grace period" that ends on the first friday of each month. I can identify the first friday: =AND(WEEKDAY(A1)=6,DAY(A1)<=7) and the first week: =AND(DAY(A1)<=7) but can't get my mind around selecting all the days of the month prior to the first friday... Thanks, Paul =AND(A1(A1-DAY(A1)), A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2))) --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
On Wed, 13 Dec 2006 22:20:49 -0500, Ron Rosenfeld
wrote: On Wed, 13 Dec 2006 16:51:00 -0800, PaulRMcHanJr wrote: I am trying to create a calendar with a "grace period" that ends on the first friday of each month. I can identify the first friday: =AND(WEEKDAY(A1)=6,DAY(A1)<=7) and the first week: =AND(DAY(A1)<=7) but can't get my mind around selecting all the days of the month prior to the first friday... Thanks, Paul =AND(A1(A1-DAY(A1)), A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2))) --ron By way of explanation: A1-DAY(A1) last day of the preceding month A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2) Saturday after the first Friday in the month. A1(A1-DAY(A1)) A1 is after the last day of the preceding month A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2)) A1 is prior to the 1st Sat after the 1st Fri of the month The above presumes that A1 contains a date, and not a date + a time. --ron |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
Isn't this part always true?
A1(A1-DAY(A1)) Ron Rosenfeld wrote: On Wed, 13 Dec 2006 22:20:49 -0500, Ron Rosenfeld wrote: On Wed, 13 Dec 2006 16:51:00 -0800, PaulRMcHanJr wrote: I am trying to create a calendar with a "grace period" that ends on the first friday of each month. I can identify the first friday: =AND(WEEKDAY(A1)=6,DAY(A1)<=7) and the first week: =AND(DAY(A1)<=7) but can't get my mind around selecting all the days of the month prior to the first friday... Thanks, Paul =AND(A1(A1-DAY(A1)), A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2))) --ron By way of explanation: A1-DAY(A1) last day of the preceding month A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2) Saturday after the first Friday in the month. A1(A1-DAY(A1)) A1 is after the last day of the preceding month A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2)) A1 is prior to the 1st Sat after the 1st Fri of the month The above presumes that A1 contains a date, and not a date + a time. --ron -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
On Wed, 13 Dec 2006 22:03:00 -0600, Dave Peterson
wrote: Isn't this part always true? A1(A1-DAY(A1)) Duh :-)) Of course. Thinking one way and not thinking clearly. Simplifies to: =A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2)) --ron |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to find each day prior to first friday, monthly
Hello Ron,
Any chance that you could look back to?: Have a challenge with "20%" in a formula. From: "Dennis" Newsgroups: microsoft.public.excel.programming Subject: How in to parse constants in formula to cells Date: 13 Dec 2006 11:34:37 -0800 TIA Dennis Ron Rosenfeld wrote: On Wed, 13 Dec 2006 22:03:00 -0600, Dave Peterson wrote: Isn't this part always true? A1(A1-DAY(A1)) Duh :-)) Of course. Thinking one way and not thinking clearly. Simplifies to: =A1<(A1-DAY(A1)+9-WEEKDAY(A1-DAY(A1)+2)) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
| Rolling Monthly Amounts to Annual Monthly Amounts | Excel Discussion (Misc queries) | |||
| Find Function | Excel Discussion (Misc queries) | |||
| I have a list of numbers (bus miles) I want to find the monthly a. | Excel Discussion (Misc queries) | |||
| Date Calculations | Excel Worksheet Functions | |||
Hybrid Mode
