# Year/Month/Week

Folks

how can i have a formula show from column D1 the current year,D2 current month,D3 current week and then copy this accross to the right accross the worksheet for 52 weeks?
###### Who is Participating?

Commented:
ok then here it is. Note that in row 2 the days are the first day of the corresponding week in row 3 !

Enjoy
gowflow
ganntV07.xlsm
0

Hi rutgermons,

what ever I understand

for current year =YEAR(NOW()))
for Month=MONTH(NOW())
For Day=DAY(NOW())

Thanks
0

Commented:
well here it is:

if it is for year then the formula in D1 will be
=Year(today())

if it is for Month then the formula in D2 will be
=Month(today())

if it is for Week then the formula in D3 will be
=Weeknum(today())

All this will give you the year of today 2014 the month of today 02 and the week of today 5

Now what I have a problem understanding is why you want to drag this for 52 weeks ?? you will endup with the same value across 52 columns which are the same values you have in D1, D2, D3

What I feel that you want instead is to have for the weeks 01, 02 03 .. etc ... the corresponding Month and year am I correct ? or else pls clarify what you need.

gowflow
0

Author Commented:
building a gantt chart in excel ,

based on colums b and c (start and end date) we need to display an "x" notation and highlight in yellow if  weeknum in range d2:bc2 > =start and <= end date are true  of the corresponding start and end date, this allows the plan to be more dynamic based on todays date
0

Commented:
The Weeknum function does not always give the correct result.
It starts with weeknumber 1 for January 1. even if the calendar week is 52 or 53.
So the first week is 1 to 7 days.
Use this to calculate the standardised ISO weeknumber.
``````=INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)
``````
A1 is the date. Replace A1 with TODAY() for current day.

The reverse function, getting the date from year and weeknumber is
``````=IF(((DATE(A1,1,1)-MOD(DATE(A1,1,1)-2,7)+(7*(MOD(DATE(A1,1,1)-2,7)>3)))-1)+(A2*7)-6=DATE(A1+1,1,1)-MOD(DATE(A1+1,1,1)-2,7)+(7*(MOD(DATE(A1+1,1,1)-2,7)>3)),NA(),((DATE(A1,1,1)-MOD(DATE(A1,1,1)-2,7)+(7*(MOD(DATE(A1,1,1)-2,7)>3)))-1)+(A2*7)-6)
``````
A1 is the year (use 4 digits for year, like 2014), and A2 is the weeknumber.
The formula finds the first day of the week (Monday).
0

Commented:
gowflow
0

Author Commented:
gowflow

"What I feel that you want instead is to have for the weeks 01, 02 03 .. etc ... the corresponding Month and year am I correct ? or else pls clarify what you need"

correct, but this should reflect the current week (week 6 for example) as week 1
0

Commented:
Say this again ? You always want to have in Col D the actual week which would be labeled week 1 ???

Like this week you would have week 1 and next week on Tuesday say you would also have Week 1 in Col D like you want the time to move from right to left  .... ???

But in your example you would loose track of when issue started as last week and the wek before would not show.

I am not at all clear. Isn't it the same as the past workbook you had ? which is Week 1 the real week 1 of the year etc ... ?
0

Commented:
@rutgermons
Just a side note you should make sure to always close your questions I have in the list 4 questions I participated to that are still open.

02/01/14  500  Year/Month/Week                             rutgermons    7
01/22/14  500 conditioning formatting                    rutgermons  16
10/28/13  500 excel macro select from combo/s… rutgermons    4¿
10/19/13  500 excel batch over network                  rutgermons    4

If you want to make sure that Experts always attend your questions, you should also do your part and close questions properly.

Regards
gowflow
0

Author Commented:
well, I don't have satisfactory answers for them,how do u expect me to close them?
0

Commented:
ok fine as long as it is that it is your right. The past question is still unsatisfactory to you ?
gowflow
0

Author Commented:
the below/attachws is what I am after, see jpeg file
MonthDay.jpg
0

Author Commented:
thank you for the great solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.