Solved

Year/Month/Week

Posted on 2014-02-01
13
280 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 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

12 Experts available now in Live!

Get 1:1 Help Now