Get date by month, week number, weekday and year

[Ana_Cata23]

I have this type of data:

Month Week DayOfTheWeek Year
Dec 5 Wednesday 1993

I would like to get the exact day that is - 29/12/1993

Thank you for your help

[takbb]

Hi @Ana_Cata23 , and welcome to the KNIME community!

For convenience of anybody wishing to assist, I am posting here a calendar for your example date as it makes it easier to visualise, and I’ve put markers for the week number in the month based on your example.

Some questions:

What convention are you using to determine when the “first week of the month” starts? Is it just the first week that contains any day, or is it the first week that contains a week day? Evidently it isn’t the first full week because 1 Dec 93 is a Wednesday.

What convention are you using for the start and end days of a week? e.g. does a week START on a Sunday as in the picture above, or end END on a Sunday?

thanks

[Adrix]

HI,
The day of the week does not add information.
So, I Would get rid of it using By using cell splitter , and would get the dates again with the column aggregator.
Finally , You will end up with column that can be easily converted ( I have a feeling that with the correct expression only the final node would do the job)

Here it is anyway : The last column is date column so you will see as your Dates definition

PS :
Cell Splitter- *blank space * as delimiter
and aggregator , with the option concatenate and delimiter blank space

[iCFO]

I think the question is more complex than the above solution. It looks to me like week 5 and the day of the week would combine to locate the 29th day of December on that given year. That means that the day calculation would need to be cross referenced with a historical calendar that had week count / day of week info. As @takbb mentioned, that calculation requires a definition of how weeks are divided. (I am one of those people who likes to split my weeks on Monday instead of Sunday for clean work week organization. My wife finds this odd idiosyncrasy to be less than endearing )

[takbb]

Yes as you say @iCFO this isn’t quite such a simple date task, but provided we know the rules of what constitutes being in ”week 1" of a given month and what day is considered the first day of the week, we can find out what day of the week day 1 of a given month is and then the rest is actually a relatively straightforward calculation.

[bruno29a]

Absolutely @iCFO @takbb , all 4 information provided are necessary to get to 29/12/1993, and that’s perfectly illustrated by @takbb calendar there.

@takbb in terms of what constitutes being “week 1” of a month, it’s usually the week where the 1st day of the month falls in, anywhere within that week.

But in the end, it is quite straight forward, as String to Date&Time actually supports Week of month with the placeholder “W”. The only thing that it does not support is Day of week Name, at least I’ve not seen any. As a workaround, I had to convert the Wednesday to 4. I could not find a way to do this apart from doing it manually via the Rule Engine.

@Ana_Cata23 Welcome to the Knime Community. After adding the above conversion, here’s what my workflow looks like:

I used your same sample data:

And I get the results that you expected:

Here’s the workflow:

Get date by month, week number, weekday and year – bruno29a

EDIT: This one’s for you @takbb:
Re: What constitutes being “week 1” of a month.
Just to confirm what I said already, here’s a test where I put Monday for week 1 for Dec 1993:
If we look at your calendar, December’s first week is that first row that you have, except that the Monday in that calendar week falls in November, and precisely on 1993-11-29, which is exactly that was returned

Adding more samples:

[takbb]

Thanks @bruno29a, excellent analysis as always. I hadn’t spotted ‘week of month’ in the String/Date conversion. I knew week of year was there but not week of month… and wasn’t sure if there was a convention for it. So anyway nice summary and workflow.

The only question mark then for me is the “day of the week” convention. From what I can see, there appear to be two competing ‘standards’.

On the one side we have weeks that start on a Sunday: Excel’s WEEKDAY() function returns 1 for Sunday through 7 for Saturday,

On the other side we have Java’s java.time.DayOfWeek Enum which apparently uses the ISO-8601 standard to numbers them as 1 for Monday through 7 for Sunday…

BUT… even worse than that… we have discovered that @iCFO and his wife fall on different sides of this argument!

This only matters I guess if the day being enquired about is a Sunday (or Mr & Mrs @iCFO are arguing over plans for what to do at the end of the week!), as we then need to decide which way to go, and which week Sunday falls into.

The nice thing about your approach is if choice made doesn’t suit, a simple edit of the Rule Engine is all that is required.

thanks again. Good job!

[takbb]

It’s funny how sometimes similar events coincide. Totally unrelated to this (and in fact unrelated to KNIME) I just had a work conversation about a particular report and somebody was discussing that the report should start at “the first week of the year”. The discussion then followed about how this was defined.

Apparently according to the ISO 8601 definition of “first week of the year” is the first week having the majority of its days in the new year, and with ISO weeks starting on a Monday, this means that the first week of the year is the week that contains the first Thursday (being day 4 out of 7 of the week) in the new year.

Unfortunately ISO 8601 (or so I’ve read, I haven’t been through it myself!) doesn’t give a standard definition of “first week of the month” though, but there are various suggestions I have seen:

One is that we should extend the same logic as for “first week of the year”, so the first week of a month is the first week containing a Thursday in the new month.

Some people suggest it is the first week containing a Monday (being the first full week) in the month whilst others suggest it is where the first day of the month (no matter which day) is in the new month.

Life can be complicated sometimes Anyway, glad to know that, as @bruno29a said, there is a “standard definition” within the KNIME String to date&time node, so we don’t have to worry. (Unless the OP is wanting to use a different convention…)