Solved

Year/Month/Week

Posted on 2014-02-01
13
283 Views
Last Modified: 2014-02-03
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
Comment
Question by:rutgermons
13 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39826506
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
 
LVL 29

Expert Comment

by:gowflow
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

by:rutgermons
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

by:Ejgil Hedegaard
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)

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
 
LVL 29

Expert Comment

by:gowflow
ID: 39827004
I don't get your comments !!! so unclear what you want.
gowflow
0
 

Author Comment

by:rutgermons
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
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

by:gowflow
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

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

Expert Comment

by:gowflow
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

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

Accepted Solution

by:
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

by:rutgermons
ID: 39829005
thank you for the great solution
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 98
text replacement instead of two macros only one macro 2 22
InternetExplorer object in Excel VBA. 4 22
Automating Excel Weekly Report 13 64
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now