days of the month

  • Thread starter Thread starter blaster
  • Start date Start date
B

blaster

I have been using excel to make up a work shedule and
would like to fiqure out a way to automate the inserting
the days of the month.

An example would be that say the month and year was put
into A1 then automatically excel would insert all the days
for that month into row 3 starting at B3.

This way it could be used as a template and I would not
have to fiqure out how many days each month has.

I have been reading in this group for a while and think
you guys are great for giving so much of your time helping
others!!

Thanks for any help offered. Oh and please don't post any
links for the answer. I am at work with limited internet
access (microsoft sites only).


Blaster
 
It can be done using the worksheet cahnge event.

The exact coding will depend on the exact format of data entered into
A1 and if excel sees A1 as a date or text entry
 
in cell A1 type your date ie Mar-2004 or 1/3/2004 or any
other way that cell A2 shows the first day of the month
Cell A2=A1 but is formatted in the full day month year or
whatever.
Cell b2 has the formula =A1+1,copy this formula across the
page for as many cells as you need.In A3 I have =A1 but
formatted for month year
In A4 i have =day(A2)and this is also copied across the
page....Hide rows 2 and leave out row 3.The only downside
i have found is you may get one or two days of the next
month showing
 
Hi Blaster!

Put the year in A1
Put the Month Name in full in A3
In A4 put 1
In B4 put:
=IF(A4="","",IF(A4=DAY(DATE($A$1,MATCH($A3{"January","February","March
","April","May","June","July","August","September","October","November
","December"},0)+1,0)),"",A4+1))
Copy across to AE4
Format General

In A5 you can use:
=IF(A4="","",TEXT(WEEKDAY(DATE($A$1,MATCH($A3,{"January","February","M
arch","April","May","June","July","August","September","October","Nove
mber","December"},0),A4)),"ddd"))

This will return the day of the week.

To get this for the whole of a year I can send you the Bernie Deitrick
approach which is more efficient than the above formulas.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks for all your help. I was hoping to get a formula to
make it work. Did you try this. I tried it but I get an
error when putting the formula into B4. it does not seem
to like the march in A3. Maybe I dont have it formated
correct?

Thanks again Blaster
 
thanks for your help. I was really hoping for something
that would eliminate any errors when putting in the days
such as the wrong number for the month. I was wondering if
end of the month feature might work some how such as EOM-1
ect.
 
I have been using excel to make up a work shedule and
would like to fiqure out a way to automate the inserting
the days of the month.

An example would be that say the month and year was put
into A1 then automatically excel would insert all the days
for that month into row 3 starting at B3.

This way it could be used as a template and I would not
have to fiqure out how many days each month has.

I have been reading in this group for a while and think
you guys are great for giving so much of your time helping
others!!

Thanks for any help offered. Oh and please don't post any
links for the answer. I am at work with limited internet
access (microsoft sites only).


Blaster
Click to expand...

In A1 put the date of the first day of the month: e.g. 1/1/2004.

B3: =A1
C3: =IF(B3="","",IF(MONTH(B3+1)=MONTH(B3),B3+1,""))

Fill right to AF3 (total of 31 cells).


--ron
 
which would work best I am open to any options or
opinions. The sheets I am working on need to be redone so
what ever makes the most sence will work for me.

Thanks again for your help!
 
Thanks ron'

that works well. Next question is how do I get it to
display just the day and not the month and year.

Thanks again
Blaster
 
Sorry for my ignorance but how would I apply the custom
format of dddd???

Thanks for your help
Blaster
 
Hi
- select the cell
- goto 'Format - Cells - Numbers'
- choose 'custom' as category and manually enter DDDD in the right
textbox
 
Thanks ron'

that works well. Next question is how do I get it to
display just the day and not the month and year.

Thanks again
Blaster
Click to expand...

Format/Cells/Number/Custom Type: dddd


--ron
 
Back
Top