Convert Date to 'Month name' and 'Week Number'

Ask for Help
1

Hi,

I’ve used the WEEKNUM function to get a week number from a date, so 22/3/2023 becomes week ‘12’. What I need though is this to write to two columns, ‘month’ and ‘week’. So this would instead write ‘March’ and ‘Week 4’. Can this be done?

I also have the column that the date picker is writing to, set to ‘date only’. However, a second template column that is reading from this date also writes the time. Can I stop that?

Also, can I change the format from 3/22/2023 to 22/03/2023?

2

Are you sure that you’re using a Date picker, and not a DateTime picker? I would check that first.

A simple way to handle this is with the Format Date plugin. However, be aware that has been known to give inconsistent results, especially if you have users in different regions with different regional date/time settings.

Personally, when I need to get a month name from a date, I do it using the following steps:

  • Firstly, I create a column (anywhere) with a list of month names, starting from January, one row for each month.
  • Then I determine the month index number from the Date using a Math column with the following formula: Month(Date)-1
  • And finally, I use a Single Value column that targets my list of month names column, taking N from start where N is the month index.

I have found the above to be the most reliable method.

Again, you can use the Format Date plugin for this, although it shouldn’t be necessary as all date formatting should usually respect the individual users device/browser regional settings.

1 Like
3

Hi Darren,

Thank you for your reply. I am definitely using only the date picker (see attached screenshot). However, the second template column that is copying the first column adds the time (although not the ‘real’ time, always midnight.

image

image

I’ll try the plugin to change the date format, and try your suggestion re; the week numbering. Thank you!

4

Okay, try using a Math column instead of a template.

5

What would you suggest to put in the ‘Configuration’ and ‘replacements’?

6

Yes, that works!! Thank you

image
image764×517 26.4 KB

1 Like
7

Yeah, sorry I should have suggested that first. The nice thing about using a math column is that it’s still a valid date, and so it can be used in any subsequent date/time comparisons. When you use a template it gets converted to a string. Both are useful, depending on what you intend to do with the result.

2 Likes
8

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.