Starting new month

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved
=$A$1+CHOOSE(WEEKDAY$A$1),32,31,30,29,28,27,26,25,24,23)
The above formula is part of my calendar. The problem is
at the end of the month this formula goes to the
following month. I tried the below but puts #NAME?
=IF(DAY(Calendar)>15,IF(ROW()=5,"",Calendar),IF(DAY
(Calendar)<15,IF(ROW()>8,"",Calendar),Calendar))

Question what am I doing wrong or is there another
solution please, also at the beginning it has #value!
=$A$1+CHOOSE(WEEKDAY($A$1),0)until the first day of the
month

Thankyou.
 
Hi

What exactly do you want to get on your worksheet? And what contains the
cell A1?
 
Hello Arvi My attempt in building a Calendar
A1 is a date in this case 1-Apr
E1=TEXT(E2, "ddd")
E2=$A$1+CHOOSE(WEEKDAY($A$1),0)which is Sunday
In this case 1-Apr starts on a Thursday so if in A1 I had
1-Feb E2 as this is the first day of the month the
formula returns Sun which is what it should be doing
therefore been putting 1-Apr I am getting #VALUE!
I've tried putting in =(ISERROR)($A$1+CHOOSE(WEEKDAY
($A$1),0))hoping this will leave the cell blank but it is
putting in #NAME?. I've used The CHOOSE FUNCTION because
I'm using a Sun-Sat week and I insert 9 Rows before I
start the Second week Sun-Sat insert 9 rows. I am using
only 1 month calendar. Can you give me answer how to
blank the cells.
Thankyou.
 
Hi

In Sheet1!$A$1 you have date - first of month - formatted as mmm.yyyy
In range Sheet1!$B$3:$H$3 you have names of days (Sunday, Monday, ...,
Saturday)
In range Sheet1!A4:A9 you have weeks (Week1, Week2, ..., Week6)

Select cell B2, and without leaving the cell, define named ranges (so you
can avoid too long formulas)
StartDate=Sheet1!$A$1+1-WEEKDAY(Sheet1!$A$1)
Weekdays=Sheet1!$B$3:$H$3
RowNum=ROW(Sheet1!B4)-ROW(Sheet1!B$4)
ColNum=MATCH(Sheet1!B$3;Weekdays;0)-1

Into cell B4 enter the formula
=IF(MONTH(StartDate+ColNum+7*RowNum)=MONTH($A$1),StartDate+ColNum+7*RowNum,"
")
and format the cell as "dd"

Copy the cell B4 to range B4:H9. Is the result what you wanted?
 
Hello Arvi

Thankyou yes

Just two situations 1, I need it to be a complete calendar
ie Apr-2004 starts on 11th I need it to start on the 1st
2, now is it possible with you calendar to insert rows so
that Row A5 will Start at Row A16, Row A6 at A27, Row A7
at A38,Row A8 at row A49,and finally Row A9 at Row A60.
My objective is to put data in between the weeks
Your Calendar is what I was trying to acheive and I
thankyou for taking timeout to do this for me.

Thankyou.
 
Hello Arvi

I have worked through the issue and have done it
You do not have to reply now as there is no need.

Thankyou very much for all your help.
 
Hi

Thanks for your response, I'm glad I could help you.

As about having days after every 11 rows, there are several ways to do this.
On fly, I'll modify the named range RowNum
RowNum=INT((ROW(Sheet1!B4)-ROW(Sheet1!B$4))/11)
 
Thanks Arvi.
-----Original Message-----
Hi

Thanks for your response, I'm glad I could help you.

As about having days after every 11 rows, there are several ways to do this.
On fly, I'll modify the named range RowNum
RowNum=INT((ROW(Sheet1!B4)-ROW(Sheet1!B$4))/11)

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)






.
Click to expand...
 
Thanks Arvi.
-----Original Message-----
Hi

Thanks for your response, I'm glad I could help you.

As about having days after every 11 rows, there are several ways to do this.
On fly, I'll modify the named range RowNum
RowNum=INT((ROW(Sheet1!B4)-ROW(Sheet1!B$4))/11)

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)






.
Click to expand...
 
Back
Top