How to find the week number of a date in a month and same week(number) in previous month

[mani]

I have a date field in "%m/%d/%Y" format.

I need to find the week number of this date and find the same week number of previous month.

For example:

my_date = 07/13/2020 - day in 3rd week of July 2020 (07/12/2020 to 07/18/2020)

I need to get values of 3rd week of June 2020 (06/14/2020 to 06/20/2020)

@All, Please help. Appreciate quick response.

 @niketnilay  @lspringer  @carasso 

[javiergn · Accepted solution]

Hi @mani ,

See if the following helps:

| makeresults | fields - _time
| eval my_date = strptime("07/13/2020", "%m/%d/%Y")
| eval my_date_week = strftime(my_date, "%V")

| eval my_date_month = relative_time(my_date, "@mon")
| eval my_date_month_week = strftime(my_date_month, "%V")
| eval my_date_weekNumber_inMonth = my_date_week - my_date_month_week

| eval my_date_previous_month = relative_time(my_date, "-1mon@mon")
| eval my_date_previous_month_week_start = relative_time(my_date_previous_month + (7 * my_date_weekNumber_inMonth * 86400), "@w0")
| eval my_date_previous_month_week_end =  my_date_previous_month_week_start + (7 * 86399)

| fieldformat my_date = strftime(my_date, "%m/%d/%Y")
| fieldformat my_date_previous_month_week_start = strftime(my_date_previous_month_week_start, "%m/%d/%Y")
| fieldformat my_date_previous_month_week_end = strftime(my_date_previous_month_week_end, "%m/%d/%Y")

| table my_date, my_date_previous_month_week_start, my_date_previous_month_week_end

Output: 

Couple of things: 

* I am assuming your week starts on Sunday

* 1 day has 86400 seconds but I am subtracting 1 second on line 9 to ensure your date ends on the last second of that week. That is, 06/20/2020 at 23:59:59, instead of ending at 06/21/2020 at 00:00:00 and therefore displaying 21 instead of 20.

Let me know if that helps.

Regards,

J

[mani]

Thanks @javiergn.

It's working as expected.