This is kind of tricky, because Month and Week concept conflict each other and
- need to ignore month data
- then need cumulative calc to count number of weeks from start of the year.
First Saturday of year.
Count number of weeks from start of year.
End of week
Actually, you don't need a table calc for this. The month and week numbers are sufficient, as long as you compute the date carefully:
DATEADD('day',7*([F Week]-1)+6,DATETRUNC('week',DATEPARSE("MMMYYYY",[F Month]+[F Year]),'saturday'))
To explain in a bit more detail:
DATEPARSE converts the month abbreviation and year to a date.
DATETRUNC finds the Saturday at the start of the week
DATEADD shifts the date by the right number of weeks and also adds 6 to get to the last day of the week as requested.
I'm currently on 10.5 and looks like DATEPARSE doesn't work
if I drop Date in it's place it looks like the following works
date(datetrunc('week',(date([F Year]+[F Month]+"01")),'saturday') +(int([F Week]))*7)
any problems with using this?
DATEPARSE is only available on some databases. It works for Excel as in your example, but not on whatever others you may use.
DATE should be fine, though.
It does not work cause MMM is not int value.
Which connection are you using?
I would think most databases can handle the MMM format but that doesn't mean all will. This is why we have DATEPARSE, but still not every database supports it.