Calendar dates in excel

[d9pierce]

Hi all and thanks for your help!

I may not have explained this well in my last post so I am re posting
this question with a more clear view.

My company uses a spread sheet for a schedule bar chart type of thing.
I have the following:

Feb, 09
M T W T F M T W T F
2324252627 2 3 4 5 6

and so on....

These are set in individual cells and then an array of cells below to
fill in with shade for the schedule. What I am trying to accomplish is
to insert a start schedule date so I just have to install a date
once.The M, T, W.... can be set text as that wont change, I need to
fill in the day number such as 23, 24, 25 from that date and install
the Feb, 09, Mar, 09 and so on above the M,T, W..... like a calendar.
I can get the first number day in the first cell but cannot add to it.
I am using a cell L8 for the schedule date, and Line 13 starting with
G, H, I, J,.... for the cells. I have this in G13

=Text(L8,"d") and that produces 23 which is correct

I cant get it to add to it in clanedar days for the next cells in
increments of +1, +2.... and dont know how to get the Feb,09 , Mar,09
above the other cells.

Would really love some assistance with this as currently it is set up
to change every day, date, and such and is a royal pain as there are
like 40 cells to change everytime a scheudle is created. Thanks,
Dave

 

[Pete_UK]

Hi Dave,

see my reply to your other post.

An alternative approach is in G13 to have:

=L8

and apply a custom format to the cell of d. Then in H13 you could
have:

=IF(WEEKDAY(G13+1,2)>5,3,1)+G13

and format this as d. This avoids the weekend dates. Then just copy
this across as far as you need.

Hope this helps.

Pete

 

[d9pierce]

Hi Dave,

see my reply to your other post.

An alternative approach is in G13 to have:

=L8

and apply a custom format to the cell of d. Then in H13 you could
have:

=IF(WEEKDAY(G13+1,2)>5,3,1)+G13

and format this as d. This avoids the weekend dates. Then just copy
this across as far as you need.

Hope this helps.

Pete











- Show quoted text -

Hi all,
OK, I figured out how to get the month and year in my cells above, gee
do I feel stupid right now!

In any event, I used Pete's formula and that worked well for giving me
the full dates but how do I add
the "d" to this formula to give me just the day # such as 1, 2,
3,,....29,30,31....instead of the full date?

Thanks
Dave

 

[Pete_UK]

Dave,

as you replied directly to me I've sent you a little mock-up of what I
think you want. With a date in a cell, you can click on Format | Cells
| Number tab and then choose Custom (at the bottom of the list). In
the panel you just need to enter "d" (without the quotes) to display
just the day part of the date.

Hope this helps.

Pete

 

[d9pierce]

Dave,

as you replied directly to me I've sent you a little mock-up of what I
think you want. With a date in a cell, you can click on Format | Cells
| Number tab and then choose Custom (at the bottom of the list). In
the panel you just need to enter "d" (without the quotes) to display
just the day part of the date.

Hope this helps.

Pete







- Show quoted text -

Just thank you to all, made this real simple!
Have a great day!
Dave

 

[Shane Devenshire]

Hi,

Since we don't know what solution you got because its off line, here is
another (the same?) approach

In cell A3 enter the starting date as a date

in cell B3 enter =A3+1 and copy this to the right.
Select the row 3 data and choose Format, Cells, Number tab, and enter D in
the Type box.

In A2 enter =LEFT(TEXT(A3,"DDD")) and copy it to the right.

In cell A1 enter the formula =A3.
In cell B1 enter the formula =IF(MONTH(A3)=MONTH(B3),"",B3) and copy this to
the right.

Select the row 1 data and choose Format, Cells, Number tab, and enter mmm,
yy in the Type box.

 

[Pete_UK]

You're welcome, Dave - thanks for feeding back.

Pete