Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
I have a spreadsheet where you enter the month. The spreadsheet is organised
into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell 2.)the opening date of the month in week 1 worksheet 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet 4.) the closing date of the end of the month in week 5 worksheet Grateful for any help - thanks -- Every decision you make - is a decision about who you are |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
I fyou enter the number of the month in A1 (1 TO 12) and the year in b1 = 2009.
the date is the following =DATE(B1,A1,1) the last day of the month is =EOMONTH(DATE(B1,A1,1),0) The 1st Saturday of the month is =DATE(B1,A1,1)+(7-WEEKDAY(DATE(B1,A1,1),1)) I'm not sure from your request if the opening and closing days are suppose to be Sunday and Saturday and if the first saturday of the month of march can be a date in February. Need to see your exact calendar to get the formulas correct. "George B" wrote: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell 2.)the opening date of the month in week 1 worksheet 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet 4.) the closing date of the end of the month in week 5 worksheet Grateful for any help - thanks -- Every decision you make - is a decision about who you are |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
Month No being in A2:
€˛George B€¯ ezt Ć*rta: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell =DAY(DATE(YEAR(TODAY()),$A$2+1,0)) 2.)the opening date of the month in week 1 worksheet =DATE(YEAR(TODAY()),$A$2,1) 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet =DATE(YEAR(TODAY()),A2,1+7*1)-WEEKDAY(DATE(YEAR(TODAY()),A2,8-7)) (from Bob Philips) 4.) the closing date of the end of the month in week 5 worksheet =DATE(YEAR(TODAY()),$A$2+1,0) Regards, Stefi |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
Assuming that you have year in A1 and month in A2 the formulas a
1) =DATE(A1,A2+1,1)-DATE(A1,A2,1) 2) =DATE(A1,A2,1) 3 =DATE(A1,A2,8-WEEKDAY(DATE(A1,A2,1))) 4) =DATE(A1,A2+1,1)-1 Basically the formula will give the output in the date format and you may have to use format cells to make it as number. I am not sure whether I understood your queries correctly. Correct me if I my understanding is wrong as I am new to this group. Regards, Ram. "George B" wrote: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell 2.)the opening date of the month in week 1 worksheet 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet 4.) the closing date of the end of the month in week 5 worksheet Grateful for any help - thanks -- Every decision you make - is a decision about who you are |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
Hi Joel
thanks for the help. The formulas that I used were 1,2 and 4 from Stefi and no 3 from Ram. I could not get eomonth to work - my version of excel is 2003(should have mentioned in my post -sorry) thanks -- Every decision you make - is a decision about who you are "Joel" wrote: I fyou enter the number of the month in A1 (1 TO 12) and the year in b1 = 2009. the date is the following =DATE(B1,A1,1) the last day of the month is =EOMONTH(DATE(B1,A1,1),0) The 1st Saturday of the month is =DATE(B1,A1,1)+(7-WEEKDAY(DATE(B1,A1,1),1)) I'm not sure from your request if the opening and closing days are suppose to be Sunday and Saturday and if the first saturday of the month of march can be a date in February. Need to see your exact calendar to get the formulas correct. "George B" wrote: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell 2.)the opening date of the month in week 1 worksheet 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet 4.) the closing date of the end of the month in week 5 worksheet Grateful for any help - thanks -- Every decision you make - is a decision about who you are |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
Hi Stefi thanks for the help. I used nos. 1, 2 and 4 all worked great. Could not get no.3 to work and so used the answer from Ram thanks george-- Every decision you make - is a decision about who you are "Stefi" wrote: Month No being in A2: €˛George B€¯ ezt Ć*rta: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell =DAY(DATE(YEAR(TODAY()),$A$2+1,0)) 2.)the opening date of the month in week 1 worksheet =DATE(YEAR(TODAY()),$A$2,1) 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet =DATE(YEAR(TODAY()),A2,1+7*1)-WEEKDAY(DATE(YEAR(TODAY()),A2,8-7)) (from Bob Philips) 4.) the closing date of the end of the month in week 5 worksheet =DATE(YEAR(TODAY()),$A$2+1,0) Regards, Stefi |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
Hi Ram
thanks for the help. I used answers no 1,2 and 4 from Stefi and no.3 from you Thanks george -- Every decision you make - is a decision about who you are "Ram" wrote: Assuming that you have year in A1 and month in A2 the formulas a 1) =DATE(A1,A2+1,1)-DATE(A1,A2,1) 2) =DATE(A1,A2,1) 3 =DATE(A1,A2,8-WEEKDAY(DATE(A1,A2,1))) 4) =DATE(A1,A2+1,1)-1 Basically the formula will give the output in the date format and you may have to use format cells to make it as number. I am not sure whether I understood your queries correctly. Correct me if I my understanding is wrong as I am new to this group. Regards, Ram. "George B" wrote: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell 2.)the opening date of the month in week 1 worksheet 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet 4.) the closing date of the end of the month in week 5 worksheet Grateful for any help - thanks -- Every decision you make - is a decision about who you are |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic date entry
You are welcome! Thanks for the feedback!
Bob's original message was: Generic formula =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW);2) Nth is the instance, 1st,.2nd etc, DoW is the serial number of the Day, Sun=1, Mon=2, etc. Maybe the problem was that you didn't take into account different numbering of days. In Bob's usage of WEEKDAY Sun=1, Mon=2. If you want Mon=1, Tue=2 type of numbering, then modify the formula: =DATE(YEAR(A1),MONTH(A1),1+7*Nth)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-DoW);2) Stefi €˛George B€¯ ezt Ć*rta: Hi Stefi thanks for the help. I used nos. 1, 2 and 4 all worked great. Could not get no.3 to work and so used the answer from Ram thanks george-- Every decision you make - is a decision about who you are "Stefi" wrote: Month No being in A2: €˛George B€¯ ezt Ć*rta: I have a spreadsheet where you enter the month. The spreadsheet is organised into weeks . The weeks run from Sunday to Saturday. I would like to be able to enter the Month and then have excel enter :- 1.) the no of days in the month in a cell =DAY(DATE(YEAR(TODAY()),$A$2+1,0)) 2.)the opening date of the month in week 1 worksheet =DATE(YEAR(TODAY()),$A$2,1) 3.) the closing date of the first week of the month (ie the first sat) in week 1 worksheet =DATE(YEAR(TODAY()),A2,1+7*1)-WEEKDAY(DATE(YEAR(TODAY()),A2,8-7)) (from Bob Philips) 4.) the closing date of the end of the month in week 5 worksheet =DATE(YEAR(TODAY()),$A$2+1,0) Regards, Stefi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 date entry: Cannot get away from d-m-yy entry format | Excel Discussion (Misc queries) | |||
Automatic Entry | Excel Discussion (Misc queries) | |||
How do I create an automatic date entry? | Excel Discussion (Misc queries) | |||
Automatic Date Entry | Excel Worksheet Functions | |||
Need help with automatic date entry | Excel Discussion (Misc queries) |