Converting Year+Week into months & year in separate column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi People,
I have a column in which I am showing the Year. Week as 2023.01 & I want to convert it into 2 separate columns One Is Just Year & in Second I want the Month instead of the week
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @UKPQ - is it just the case that you want to separate them? Or do you also want to change the format? In terms of separating, you can just use a Text to Columns tool, set the dot as the delimiter and make sure it's splitting to 2 columns, as so:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @UKPQ you can use a text to columns tool here:
set the number of columns to 2 and the set the delimeter as "."
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
To find the month based on the week number, you can use the fact that an average month has around 4.33 weeks. Given a week number, you can estimate the month by dividing the week number by 4.33 and rounding up to the nearest whole number. Keep in mind that this is an estimation and might not be precise for all cases due to the varying number of days in different months.
Here's a general formula to find the estimated month based on the week number:
Estimated Month=⌈Week Number4.33⌉Estimated Month=⌈4.33Week Number⌉
