Excel 2013 formula with dates and calendar weeks

[fmoaveni]

Hi everyone,
I am using microsoft Excel 2013 and I am having trouble with a formula I created to write the exact date based on the input from 3 other cells. I will now clarify:

In cell J14 I have the ability to write the year (2015, 2016,2017, etc).

In column G and H for example (G4 and H4) I can input the calendar week (G4 ie. 1, 2, 3, 4) and the day in the (H4 Monday, Tues, Weds, Thursday, etc). And it will find the correct date in cell "I4".

It was working perfectly for calendar year 2015, but since 2015 has 53 calendar weeks, it is making all of the 2016 weeks 1 calendar week off. It does not recognize that 2015 has 53 CWs. Is there a way to write a macro for cell I4 or column I in general to take the information from the other columns/cells and auto populate a date? Just so I don't have to mess around with really messy and long formulas? Or if you only know of a formula could you please help me out? I am struggling with it.


Thank you so much!

 

[fmoaveni]

In I4 I mean a date such as (04.01.2016) (German date structure DD.MM.YYYY) for the information in cells G4 and H4 of (G4.....1) and (H4....Monday) and in cell J14 (2016). That means year 2016, Calendar week 1, and Monday of that calendar week which equals (04.01.2016)

 

[fmoaveni]

=IF(L274="Montag";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+1);IF(L274="Dienstag";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+2);IF(L274="Mittwoch";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+3);IF(L274="Donnerstag";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+4);IF(L274="Freitag";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+5);IF(L274="Samstag";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+6);IF(L274="Sonntag";MAX(DATE($S$5;1;1);DATE($S$5;1;1)-WEEKDAY(DATE($S$5;1;1);2)+(K274-1)*7+7);"")))))))

So that is an example of the formula, and Ignore the cells. That is in column L and column K instead of G and H. and it is using S5 instead of J14. Those are the actual cells so if you are writing a macro maybe those would be great to reference. But that formula took me forever to write.

 

[MichaelS94]

[TABLE="width: 500"]
<tbody>[TR]
[TD]D1
[/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[TD]G1
[/TD]
[TD]H1
[/TD]
[TD]I1
[/TD]
[TD]J1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Calendar Week
[/TD]
[TD]Day
[/TD]
[TD]Date
[/TD]
[TD]Year
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]Monday
[/TD]
[TD]28 Dec 2015
[/TD]
[TD]2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

With formula in I1:

=((G2-1)*7)+DATE(J2,1,1)-(WEEKDAY(DATE(J2,1,1)-2))+VLOOKUP(H2,D1:E7,2,0)

Essentially the first part of the formula uses the year and week number to calculate the date of the first Monday of that calendar week. But then because we also want to be able to put in the day of the week, I used a vLookup at the end to add on zero days if a Monday (it uses Monday by default), 1 day if Tuesday, 2 days if Wednesday etc. The vlookup uses the data in rows D and E (which you may want to move onto a new sheet and hide the sheet).

Of course, change the days of the week in row D to be whatever you would enter into row H (i.e. the German days of the week I believe).

Let me know how this works for you!

 

[fmoaveni]

Hi Michael,
So I put in the formula and changed the cells so the formula reads like this:
=IFERROR(((K3-1)*7)+DATE($S$5;1;1)-(WEEKDAY(DATE($S$5;1;1)-2))+VLOOKUP(L3;T6:U13;2;0);"")

T6:U13 contains the monday-sunday
Cell S5 is the cell where I can input the year
Cell K3 is for the calendar week number (ie. 1,2,3,4,5, etc)
Cell L3 is for the day of the week (ie. Monday, Tuesday, Weds) etc.
Cell M3 is the cell that actually contains this formula to populate the date.

The dates are not calculating correctly for some reason. When I change the year to 2016 for example and input week 1, monday....it gives me the date as the 28th of December which is it actually the 4th of January.


Or if I input the day as the calendar week 4, monday....it gives me the 18th of January which it should be the 25th of january.
When I put week 5...monday though it gives me the 25th of January. I think it does the same thing as my old formula which makes the weeks one week off.

Do you know how to go about fixing it? Thank you.

 

[MichaelS94]

Sorry, my mistake - I thought that the Mon 28th December 2015 would be the first calendar week of 2016, but seems I am wrong! Very easy to fix though:

=IFERROR(((K3*7)+DATE($S$5,1,1)-(WEEKDAY(DATE($S$5,1,1)-2))+VLOOKUP(L3,$T$6:$U$13,2,0),"")

All I did was remove the "-1" which followed the first K3. In my previous version that was subtracting a week off the date to give (what I now realise was) the incorrect date of a week earlier. Also, you seem to use semicolons where you should use commas - I've corrected that.

I haven't tested this one out, but I believe it should work. Let me know!

 

[fmoaveni]

Thank you so much! That worked fantastic. And the commas weren't there because in the german version of excel I am using even though I switched it to english it requires semi-colons. Not sure why but I just wanted to clarify. Thank you again! You are a hero!

 

[MichaelS94]

No problem, glad I could help! And intereting to know about the German version - very odd!