• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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?
0
rutgermons
Asked:
rutgermons
1 Solution
 
Naresh PatelTraderCommented:
Hi rutgermons,

Cant understand your question.
what ever I understand

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

Thanks
0
 
gowflowCommented:
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
 
rutgermonsAuthor 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ejgil HedegaardCommented:
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)

Open in new window

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)

Open in new window

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
 
gowflowCommented:
I don't get your comments !!! so unclear what you want.
gowflow
0
 
rutgermonsAuthor 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
 
gowflowCommented:
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
 
gowflowCommented:
@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
 
rutgermonsAuthor Commented:
well, I don't have satisfactory answers for them,how do u expect me to close them?
0
 
gowflowCommented:
ok fine as long as it is that it is your right. The past question is still unsatisfactory to you ?
gowflow
0
 
rutgermonsAuthor Commented:
the below/attachws is what I am after, see jpeg file
MonthDay.jpg
0
 
gowflowCommented:
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
 
rutgermonsAuthor Commented:
thank you for the great solution
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now