Automatic date entry

[George B]

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

 

[Joel]

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.

 

[Stefi]

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

 

[Ram]

Assuming that you have year in A1 and month in A2 the formulas are:

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]

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

 

[George B]

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

 

[George B]

Hi Ram
thanks for the help.
I used answers no 1,2 and 4 from Stefi and no.3 from you
Thanks
george

 

[Stefi]

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: