Solved: Today() < 1st. of April

BOTFORCE · 01-02-2022 02:41 PM

Hi guys,

I’m struggling with a date-time expression.

I need in my if-statement the comparison if the [registration date] is before (or smaller than) 1.st of April of the registration date year

Any suggestions?

Thx.
Robert

Suvrutt_Gurjar

Thank you. Your date format is 1.10.2022 and not 01/10/2022 in the app. It sounds that the date format mismatch is there. I think after 3rd January the rate of post-April kicks in because I think somewhere 1.4 ( 4th January) is treated as 1st April. So please check for data format mismatch.

My device /Gsheet has mm/dd/yyyy or dd/mm/yyyy format for so will not be able to exactly replicate your scenario. Does changing the data format in the expression to dd.mm.yyyy help?

View solution in original post

Suvrutt_Gurjar

This?

For mm/dd/yyyy date system

AND( [Registration Date]<DATE(“04/01/”&YEAR( [Registration Date])) ,
[Registration Date]>=DATE(“01/01/”&YEAR( [Registration Date]))
)

For dd/mm/yyyy date system

AND( [Registration Date]<DATE(“01/04/”&YEAR( [Registration Date])) ,
[Registration Date]>=DATE(“01/01/”&YEAR( [Registration Date]))
)

BOTFORCE

Thx so far, but still not there yet 100%.

I have a a client who sells cemetery service/maintenance offering starting from April till end of October each year. So this is a period of 213 days.

When the client sells the service between january and End of march, it need to calculate the full price (100%).

If a client wants the service and it starts for example in Mai, the I want to calculate the dayprice of the services * the days left until end of october (prorated). Here is the expression:

IF(
AND(
[RegistrationDate]<DATE(“01/04/”&YEAR( [RegistrationDate])),
[RegistrationDate]>=DATE(“01/01/”&YEAR([RegistrationDate]))),
SUM([Services][Price]),
(SUM([Services][Price])/213)) * ((HOUR(DATE(01.11.2022) - [registrationdate])/24)
)

Problem:

If I change the service date between 1.1 and 4.1 it calculates 100%, after 4.1 then it calculates totaly wrong

Dateformat: DD/MM/YYYY

Thx.

Suvrutt_Gurjar

Thank you for the additional details. Well, the expression shared earlier was exactly meant to take care of the details shared earlier.

You have shared a more complex requirement with those additional details and I believe the community could still share insights. However, you may wish to share more relevant details with specific examples when the date overlaps, do all the service periods end always on 31 st October, are there any overlaps between service periods when the service price is different before 31st March and after that, etc.

BOTFORCE

Thank you for our replay. I will provide an example:

Service bought at 2nd. January → [registrationdate] then calculate SUM([Services][Price])
Service bought at 4th. April → [registrationdate] then calculate SUM([Services][Price]) / 213 (days between 1.4. and 31.10) * days left until 31.10.

Service bought after 31.10, then nothing.

There are no overlaps. The service period is each year from 01.04.YYYY until 31.10.YYYY

Suvrutt_Gurjar

Thank you. Still you may wish to clarify below.

days left untill 31.10. Are these days from 4 the April in this example?

And if service is bought on 29th March, then what will be the price? Will it be SUM([Services][Price])

BOTFORCE

Yes - in this example it need to calculate the days from april 4 - 31.10

If I would buy on the 7th June. Then I need the number of days between 7.th of June and 31.10.

Suvrutt_Gurjar

IF(
AND(
[RegistrationDate]<DATE(“01/04/”&YEAR( [RegistrationDate])),
[RegistrationDate]>=DATE(“01/01/”&YEAR([RegistrationDate]))),
SUM([Services][Price]),
(SUM([Services][Price])/213.00)) * (HOUR(DATE(“01/11/2021”)-[RegistrationDate])/24)
)

Your overall expression appears to be correct. Please try above. Have introduced decimal point in the divisor, 213.00 , assuming SUM([Services][Price]) can be possible with two decimal points.

If you still face issues, would you please update exactly what problem you are facing?

BOTFORCE

I had one parenthese error. The one directly after the 213 was double, so I removed one. Then I checked the results and same effect. Please find here below the screenshot of the data on 01.01.2022 and on 5.01.2022

BOTFORCE

1st., 2nd and 3rd. of January behave correct, but then at as of the 4th of January already somehow a negative value comes into play.

Suvrutt_Gurjar

Thank you. Your date format is 1.10.2022 and not 01/10/2022 in the app. It sounds that the date format mismatch is there. I think after 3rd January the rate of post-April kicks in because I think somewhere 1.4 ( 4th January) is treated as 1st April. So please check for data format mismatch.

My device /Gsheet has mm/dd/yyyy or dd/mm/yyyy format for so will not be able to exactly replicate your scenario. Does changing the data format in the expression to dd.mm.yyyy help?

BOTFORCE

Hi Suvrutt,

that was a good hint. I changed the expression-dateformat as following and now it works:

IF(
AND(
[RegistrationDate]<DATE(“04/01/”&YEAR( [RegistrationDate])),
[RegistrationDate]>=DATE(“01/01/”&YEAR([RegistrationDate]))
),
SUM([Services][Price]),
(SUM([Services][Price])/213.00) * (HOUR(DATE(“11/01/2022”)-[RegistrationDate])/24)
)

Thank you for your help and kind regards,
Robert

Suvrutt_Gurjar

You are welcome and nice to know, it works per your requirement now.