[DateFormula] Not able to find the expected result

[Joker_Hazard]

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

[apathetichell]

try  datetimeday(datetimetoday()) <=7 instead of datetimemonth(datetimetoday())<=7...

One more thing - currently the column coming out of your formula tool is a string - you may want to change that to a date.

[MattBSlalom · Accepted solution]

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.

[Joker_Hazard]

Thank you! I will try that tomorrow, my system is blocked for today

[Joker_Hazard]

You definitly have a point! I did not foresee this..

How would you solve this problem of a month starting with number 3th for example? 

Any clues?

[apathetichell · Accepted solution]

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 solution]

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