Date from Week#, Year and Day Name

[nymyth]

Hi again,

Is it possible to get the date from knowing the week number, day and year. I have a report that will give me data that shows me the following (except the last 2 columns that I am trying to work out):
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]TY[/TD]
[TD]LY[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Monday[/TD]
[TD]3242[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Tuesday[/TD]
[TD]9873[/TD]
[TD]2342[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Wednesday[/TD]
[TD]7639[/TD]
[TD]6720[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Thursday[/TD]
[TD]1348[/TD]
[TD]2348[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Friday[/TD]
[TD]3148[/TD]
[TD]1349[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Saturday[/TD]
[TD]1264[/TD]
[TD]1320[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Sunday[/TD]
[TD]4563[/TD]
[TD]6346[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Monday[/TD]
[TD]3245[/TD]
[TD]2354[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Tuesday[/TD]
[TD]2345[/TD]
[TD]7345[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Wednesday[/TD]
[TD]1247[/TD]
[TD]2356[/TD]
[TD]9[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


What I need to populate is columns TY and LY which would essentially show me the day date (so May is week 18, this year we didnt have a Monday so its 0, last year it was the 1st). I am trying to compare same selling days (so mon vs mon, tue vs tue) regardless of what date that was.

Can this be done?

Thanks in advance.

 

[arthurbr]

How is the weeknumber defined? ISO, week1 containing Jan 1st?.. There are different possibilities

 

[nymyth]

Week 1 is whatever day Jan 1st falls under

 

[markmzz]

Hi!

Try this:

For TY - use Ctrl+Shift+Enter to enter the formula

=IFERROR(SMALL(IF(WEEKNUM(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&EOMONTH(TODAY(),0))))=A2,
IF(TEXT(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&EOMONTH(TODAY(),0))),"dddd")=B2,
DAY(ROW(INDIRECT(EOMONTH(TODAY(),-1)+1&":"&EOMONTH(TODAY(),0)))))),1),0)

Fot LY - use Ctrl+Shift+Enter to enter the formula

​

=IFERROR(SMALL(IF(WEEKNUM(ROW(INDIRECT(EOMONTH(TODAY(),-13)+1&":"&EOMONTH(TODAY(),-12))))=A2,
IF(TEXT(ROW(INDIRECT(EOMONTH(TODAY(),-13)+1&":"&EOMONTH(TODAY(),-12))),"dddd")=B2,
DAY(ROW(INDIRECT(EOMONTH(TODAY(),-13)+1&":"&EOMONTH(TODAY(),-12)))))),1),0)
​

Markmzz​

 

[nymyth]

That is unreal - thanks so much, this one is going to take me some time to understand.

 

[markmzz]

That is unreal - thanks so much, this one is going to take me some time to understand.

You are welcome.

Markmzz

 

[nymyth]

Is there anyway to add a month component to the formula? I just realized if I ran last months file today (June 1st) everything shows me a zero as it is using todays date. Thanks.

 

[nymyth]

so i figured it out. Instead of TODAY(), i replaced with a cell reference to cell which i can change monthly. Seems to be working, unless someone has a cleaner idea

 

[markmzz]

Is there anyway to add a month component to the formula? I just realized if I ran last months file today (June 1st) everything shows me a zero as it is using todays date. Thanks.

Sorry, but I didn't understand. Look at this:

[TABLE="class: grid, width: 478"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]TY[/TD]
[TD="align: center"]LY[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]Quinta-feira[/TD]
[TD="align: center"]qui 31/05/2018[/TD]
[TD="align: center"]3242[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]Sexta-feira[/TD]
[TD="align: center"]sex 01/06/2018[/TD]
[TD="align: center"]9873[/TD]
[TD="align: center"]2342[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]Sábado[/TD]
[TD="align: center"]sáb 02/06/2018[/TD]
[TD="align: center"]7639[/TD]
[TD="align: center"]6720[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Domingo[/TD]
[TD="align: center"]dom 03/06/2018[/TD]
[TD="align: center"]1348[/TD]
[TD="align: center"]2348[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Segunda-feira[/TD]
[TD="align: center"]seg 04/06/2018[/TD]
[TD="align: center"]3148[/TD]
[TD="align: center"]1349[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Terça-feira[/TD]
[TD="align: center"]ter 05/06/2018[/TD]
[TD="align: center"]1264[/TD]
[TD="align: center"]1320[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Quarta-feira[/TD]
[TD="align: center"]qua 06/06/2018[/TD]
[TD="align: center"]4563[/TD]
[TD="align: center"]6346[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Quinta-feira[/TD]
[TD="align: center"]qui 07/06/2018[/TD]
[TD="align: center"]3245[/TD]
[TD="align: center"]2354[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Sexta-feira[/TD]
[TD="align: center"]sex 08/06/2018[/TD]
[TD="align: center"]2345[/TD]
[TD="align: center"]7345[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Sábado[/TD]
[TD="align: center"]sáb 09/06/2018[/TD]
[TD="align: center"]1247[/TD]
[TD="align: center"]2356[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]*************[/TD]
[TD="align: center"]***************[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]****[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[nymyth]

no worries Mark, thanks for the response but I managed to figure it out after I understood your formula. It was just a matter of changing the TODAY() portion of the formula to reference a cell that read the first day of the month.