Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
I need a formula to find the first saturday in the month. i.e. cell a1
contains the date 2006 02 01, i need to know the first saturday in feb. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1)
"Barry" wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
Chip Pearson's web site...
http://www.cpearson.com/excel/datetime.htm#NthDoW answers this for both a formula and using VBA. HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Barry" wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
On Mon, 6 Feb 2006 13:32:13 -0800, "Barry"
wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. If your date in A1 is always the first day of the month, then: =A1+7-WEEKDAY(A1) will give you the first Saturday of the month. If the date in A1 can be any date in the month, the first Saturday of that month will be given by: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1) --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
"bpeltzer" wrote in message
... This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1) Are you sure? It doesn't seem to work for me for all dates but =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8,1) seems to work OK -- HTH Sandy with @tiscali.co.uk "bpeltzer" wrote in message ... This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1) "Barry" wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
Thanks, you're right. =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+1,1) gets it.
The logic, BTW: Get to the first day of the month-- a2-day(a2)+1 -- and make an adjustment based on the weekday that the first of the month falls on -- 7-weekday(a2-day(a2)+1,1) --. The adjustment adds 0 if the first is Saturday, 1 on Friday, etc. --Bruce "Sandy Mann" wrote: "bpeltzer" wrote in message ... This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1) Are you sure? It doesn't seem to work for me for all dates but =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+8,1) seems to work OK -- HTH Sandy with @tiscali.co.uk "bpeltzer" wrote in message ... This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1) "Barry" wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
Thanks very much ron.
"Ron Rosenfeld" wrote: On Mon, 6 Feb 2006 13:32:13 -0800, "Barry" wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. If your date in A1 is always the first day of the month, then: =A1+7-WEEKDAY(A1) will give you the first Saturday of the month. If the date in A1 can be any date in the month, the first Saturday of that month will be given by: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1) --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
Thanks, Ron's suggestion seems to work best.
"bpeltzer" wrote: This seems to work: =a1-DAY(a1)+8-WEEKDAY(a1) "Barry" wrote: I need a formula to find the first saturday in the month. i.e. cell a1 contains the date 2006 02 01, i need to know the first saturday in feb. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first saturday in a month
On Tue, 7 Feb 2006 05:31:23 -0800, "Barry"
wrote: Thanks very much ron. You're welcome. Glad to help. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need cell formula to subtotal gross by month for a quarter | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Date Calculations | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |