Solved

# Year/Month/Week

Posted on 2014-02-01
280 Views
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?
0
Question by:rutgermons

LVL 8

Expert Comment

ID: 39826506
Hi rutgermons,

what ever I understand

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

Thanks
0

LVL 29

Expert Comment

ID: 39826615
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 Comment

ID: 39826670
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

LVL 21

Expert Comment

ID: 39826922
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

LVL 29

Expert Comment

ID: 39827004
gowflow
0

Author Comment

ID: 39827489
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

LVL 29

Expert Comment

ID: 39827865
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

LVL 29

Expert Comment

ID: 39828019
@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 Comment

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

LVL 29

Expert Comment

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

Author Comment

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

LVL 29

Accepted Solution

gowflow earned 500 total points
ID: 39828984
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

Author Closing Comment

ID: 39829005
thank you for the great solution
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article youâ€™ll learn how to use ExcelToWord! to copy data,charts, shapes â€¦
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.