fixing a date formula

Hi,

see attached file. In the green cell I enter a date, and a time-line below will give me breakback schedule of a project

1. In row 16, you see two 2009. One should be 2008.
2. I want the 2009 and 2008 to appear under the January, not December.
3. If I enter date February in the green cell, the whole calender goes wack, why?

Any help is appreciated.
Thanks
NYBoy

Hi

U15: =EDATE(V15,-1). Copy left to B15
V16: =IF(MONTH(V15)=1,YEAR(V15),"") Copy left to B16

If you don't have the EDATE function, then install the standard excel Analysis Toolpak addin.

rylo

Thank rylo!! I'm all set.

NYBoy,

Please read the Forum Rules about thread titles before starting your next thread, to avoid having it closed while you ponder a concise and descriptive title.

Thanks.

shg, got it! Thanks. Will have a concise title next time.
can you pls fix it this time?

Originally Posted by rylo

Hi

U15: =EDATE(V15,-1). Copy left to B15
V16: =IF(MONTH(V15)=1,YEAR(V15),"") Copy left to B16

If you don't have the EDATE function, then install the standard excel Analysis Toolpak addin.

rylo

rylo, one more question pls. I want the January of each year to stand out, let's say I want excel to color code January automatically. Is there a way to do it? Thank you.

Originally Posted by davesexcel

You can edit the title yourself, click on edit, advanced and change the title...why ask somebody else to do it or you?? You have 53 posts, you should know the rules by now!

I'm not here waste your or my time...

i would have fixed it. but
1) rule says if 2 hrs passed, ask moderator to fix it.
2) where is "edit"?

Either guide me correctly or you pls fix it.

Hi

1) On the title change, can you nominate what the title should be and I'll have it changed.

2) Have a look at conditional formatting. You should be able to do something when the cell contains "Jan". If you can't solve it, then what formatting do you want it to have? Bold, background color????


rylo

Hi rylo,
I tried the conditional formatting, it didn't work.

i want "Jan" cell and the year cell (the cell below Jan) to be gray color all the time. Is there a way to do this?

NYBoy

Hi

Following on from the above formulas

V15: Format, Conditional formatting, formula is: =TEXT(V15,"mmm") = "Jan", nominate your pattern color. Copy the format left.
V16: format, conditional formatting, formula is: =TEXT(V15,"mmm") = "Jan", nominate your pattern color. Copy the format left.

rylo

Thank you, rylo!