| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i set date as 1st thursday of every month
i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting
every 1st thursday of the month. thanks springbokiwi |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
If you want to list those dates you can put
=DATE(2007,ROW(),1)+5-WEEKDAY(DATE(2007,ROW(),1)) or =DATE(2007,ROW(),6-WEEKDAY(DATE(2007,ROW(),1))) into row 1, & copy down into rows 2 to 6. -- David Biddulph "srpingbokiwi" wrote in message ... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. thanks springbokiwi |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
Sorry, correction:
=DATE(2007,ROW(),1)+MOD(5-WEEKDAY(DATE(2007,ROW(),1)),7) -- David Biddulph "David Biddulph" wrote in message ... If you want to list those dates you can put =DATE(2007,ROW(),1)+5-WEEKDAY(DATE(2007,ROW(),1)) or =DATE(2007,ROW(),6-WEEKDAY(DATE(2007,ROW(),1))) into row 1, & copy down into rows 2 to 6. -- David Biddulph "srpingbokiwi" wrote in message ... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. thanks springbokiwi |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
"srpingbokiwi" wrote in message
... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. Assuming your 07 dates (commencing 1st Jan) are in A1:A212, enter this formula in B1: =AND(DAY(A1)<=7,WEEKDAY(A1)=5) and copy it down. Then copy column B and Edit Paste Special Values. And use Replace (CTRL+H) to replace FALSE with nothing and TRUE with (example) "Monthly meeting". Note: you'll probably need the Analysis Toolpak installed (under Tools Options) to utilise the WEEKDAY function. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
Hi David,
I think you could modify your first suggestion so that it works =DATE(2007,ROW(),8-WEEKDAY(DATE(2007,ROW(),3))) "David Biddulph" wrote: Sorry, correction: =DATE(2007,ROW(),1)+MOD(5-WEEKDAY(DATE(2007,ROW(),1)),7) -- David Biddulph "David Biddulph" wrote in message ... If you want to list those dates you can put =DATE(2007,ROW(),1)+5-WEEKDAY(DATE(2007,ROW(),1)) or =DATE(2007,ROW(),6-WEEKDAY(DATE(2007,ROW(),1))) into row 1, & copy down into rows 2 to 6. -- David Biddulph "srpingbokiwi" wrote in message ... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. thanks springbokiwi |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
Thanks Andy
that would work if i was using thing full year as a schedule following is the first 3 months, you will notice that 1st thursday of every month is Youth Leaders Meeting; 2nd Friday of every month is Youth Specific Meeting; 3rd Friday is Youth Social and Last Sunday of every month is Youth Service. This runs from Feb to November. I also have this repeated for 6 areas... Thanks for your help Bryce 1-Feb Youth Leaders Meeting 9-Feb Specific Meeting TBA Serving & Leading Seminar 23-Feb Social or Youth Activity 25-Feb Divine Service 1-Mar Youth Leaders Meeting 9-Mar Specific Meeting 23-Mar Social or Youth Activity 25-Mar Youth Service - Henderson 5-Apr Youth Leaders 13-Apr Specific Meeting TBA Serving & Leading Seminar 20-Apr Social or Youth Activity 26-Apr Youth Leaders - 7.30PM 29-Apr Youth Service - Howick "Andy Brown" wrote: "srpingbokiwi" wrote in message ... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. Assuming your 07 dates (commencing 1st Jan) are in A1:A212, enter this formula in B1: =AND(DAY(A1)<=7,WEEKDAY(A1)=5) and copy it down. Then copy column B and Edit Paste Special Values. And use Replace (CTRL+H) to replace FALSE with nothing and TRUE with (example) "Monthly meeting". Note: you'll probably need the Analysis Toolpak installed (under Tools Options) to utilise the WEEKDAY function. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
Thanks Daddylonglegs
Maybe is hould have been more specific... Following is the first 3 months, you will notice that 1st thursday of every month is Youth Leaders Meeting; 2nd Friday of every month is Youth Specific Meeting; 3rd Friday is Youth Social and Last Sunday of every month is Youth Service. This runs from Feb to November. 1-Feb Youth Leaders Meeting 9-Feb Specific Meeting TBA Serving & Leading Seminar 23-Feb Social or Youth Activity 25-Feb Divine Service 1-Mar Youth Leaders Meeting 9-Mar Specific Meeting 23-Mar Social or Youth Activity 25-Mar Youth Service - Henderson 5-Apr Youth Leaders - 7.30PM 13-Apr Specific Meeting TBA Serving & Leading Seminar 20-Apr Social or Youth Activity 26-Apr Youth Leaders - 7.30PM 29-Apr Youth Service - Howick I also have this repeated for 6 areas... Thanks for your help Bryce "daddylonglegs" wrote: Hi David, I think you could modify your first suggestion so that it works =DATE(2007,ROW(),8-WEEKDAY(DATE(2007,ROW(),3))) "David Biddulph" wrote: Sorry, correction: =DATE(2007,ROW(),1)+MOD(5-WEEKDAY(DATE(2007,ROW(),1)),7) -- David Biddulph "David Biddulph" wrote in message ... If you want to list those dates you can put =DATE(2007,ROW(),1)+5-WEEKDAY(DATE(2007,ROW(),1)) or =DATE(2007,ROW(),6-WEEKDAY(DATE(2007,ROW(),1))) into row 1, & copy down into rows 2 to 6. -- David Biddulph "srpingbokiwi" wrote in message ... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. thanks springbokiwi |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i set date as 1st thursday of every month
Thanks David
Maybe is hould have been more specific... Following is the first 3 months, you will notice that 1st thursday of every month is Youth Leaders Meeting; 2nd Friday of every month is Youth Specific Meeting; 3rd Friday is Youth Social and Last Sunday of every month is Youth Service. This runs from Feb to November. 1-Feb Youth Leaders Meeting 9-Feb Specific Meeting TBA Serving & Leading Seminar 23-Feb Social or Youth Activity 25-Feb Divine Service 1-Mar Youth Leaders Meeting 9-Mar Specific Meeting 23-Mar Social or Youth Activity 25-Mar Youth Service - Henderson 5-Apr Youth Leaders - 7.30PM 13-Apr Specific Meeting TBA Serving & Leading Seminar 20-Apr Social or Youth Activity 26-Apr Youth Leaders - 7.30PM 29-Apr Youth Service - Howick I also have this repeated for 6 areas... Thanks for your help Bryce "David Biddulph" wrote: Sorry, correction: =DATE(2007,ROW(),1)+MOD(5-WEEKDAY(DATE(2007,ROW(),1)),7) -- David Biddulph "David Biddulph" wrote in message ... If you want to list those dates you can put =DATE(2007,ROW(),1)+5-WEEKDAY(DATE(2007,ROW(),1)) or =DATE(2007,ROW(),6-WEEKDAY(DATE(2007,ROW(),1))) into row 1, & copy down into rows 2 to 6. -- David Biddulph "srpingbokiwi" wrote in message ... i am drawing up a schedule for Jan to Jun 2007. I need to schedule a meeting every 1st thursday of the month. thanks springbokiwi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching month part of date only | Excel Discussion (Misc queries) | |||
| Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
| Calculate 1st of month date from existing date. | Excel Discussion (Misc queries) | |||
| How do I break a date range by month? | Excel Discussion (Misc queries) | |||
| Return the end of month date from a date | Excel Worksheet Functions | |||
Linear Mode
