[DateFormula] Not able to find the expected result

Question

Hello all!

I have been struggling with trying to update the correct value in the field called "validação_dia".

Basically this formula was supposed to bring the values regarding the current month.

Example: IF its the first week of APRIL, THEN bring the data related to MARCH. IF its the second week of APRIL, THEN bring the data related to APRIL.

Another Example: IF its the first week of MAY, THEN bring the data related to APRIL. IF its the second week of MAY, THEN bring the data related to MAY.

But as you can see, its only bring the data related to march (2021-03-21) and it was supposed to bring from april in some cases. How can I fix this?

"Mes leilao" is the number of the month
"Data_do_leilão_DT" Is the full date.

Thank u very much

MattBSlalom · Accepted Answer

I think your expression is trying to say "if today is day 1 through 7 of the month then use last month else use this month".  Assuming that's the case, there's an error in your expression.  You currently have "IF DateTimeMonth(DateTimeToday()) <= 07", but should be "IF DateTimeDay(DateTimeToday()) <= 07".

With that said, since you're describing the scenario as being the first week of the month, checking the number of the day may or may not be what you actually want to do here.  Since weeks (Sun - Sat, or Mon - Sun, etc) frequently don't fall nicely with the first of the month, you may need to consider an alternative method to identify if the date is part of week 1 vs 2.  For example, assuming a week of Sunday to Saturday, April 2021 had it's first partial week ending on the 3rd and it's first full week ending on the 10th.

apathetichell · Accepted Answer

These work with Sundays as the first day of a week:

datetimeformat(datetimeadd([Field1],-(tonumber(DateTimeFormat([Field1],"%u"))),"days"),"%B")

datetimeadd([Field1],-(tonumber(DateTimeFormat([Field1],"%u"))),"days")

First formula gives you the month of the Sunday which starts the week.

Second formula gives you the date of the Sunday.

Obviously if you want to adjust to a Monday start subtract one from the second parameter.

Jonathan-Sherman · Accepted Answer

Hi @Joker_Hazard,

I'm not 100% sure on what outcome you're looking for but i've taken this approach, do correct me if i'm not quite on the right track!

IF 
(ToNumber(DateTimeFormat([Date],'%U'))-ToNumber(DateTimeFormat(DateTimeTrim([Date],'firstofmonth'),'%U'))+1)
= 1
THEN DateTimeAdd(DateTimeTrim([Date],'firstofmonth'),-1,'month')
ELSE [Date]
ENDIF

(1) I determine the week number within the month of the date by calculating the week number of the date (0-53) minus the week number of the first of the month of that date.

(2) Check if the week number calculated above = 1, if it does then calculate the first of the month previous

(3) If the week number calculated above does not equal 1 then return the original date.

I've attached my workflow for you to download if needed.

Kind regards,

Jonathan