Skip to main content

Calculate if time is date time is between specific date

  • January 10, 2024
  • 3 replies
  • 21 views
S
Forum|alt.badge.img+2

I have a formula that calculates a 2.99% but since January 1 is 2.29%. Actual formula: ({Total due}-(2.99*{Total due})/100)+{Manual Rate}

I have a column: Date Booked

 
How can I modify the actual column named 2.99% to be calculated only if the Date Booked is before 12/31/2023. 
And a second one to calcula only if Date Booked is after 01/01/2024?
 
Any help, please?

3 replies

S
Forum|alt.badge.img+2
  • shasixz
  • Author
  • Participating Frequently
  • January 11, 2024

I am trying my best but is not working:

    IF(
    AND(
        IS_AFTER(
            {Date Booked},
            '1/1/2024'
            ),
    {Date Booked},
    ({Total due}-(2.29*{Total due})/100)+{Manual Rate},
        IS_BEFORE(
            {Date Booked},
            '12/31/2023'
            )
        ),
    {Date Booked},
    ({Total due}-(2.99*{Total due})/100)+{Manual Rate}
    )
S
Forum|alt.badge.img+2
  • shasixz
  • Author
  • Participating Frequently
  • January 11, 2024

I am so desperate, I can not find a way to make it work. right now is only giving me the 2.29% result and the 2.99% is empty:

IF(
{Date Booked},
IF(
IS_AFTER(
{Date Booked},
DATETIME_PARSE(
'31/12/2023',
'DD/MM/YYYY'
)
),
({Total due}-(2.29*{Total due})/100)+{Manual Rate}),
IF(
IS_BEFORE(
{Date Booked},
DATETIME_PARSE(
'31/12/2023',
'DD/MM/YYYY'
)
),
({Total due}-(2.99*{Total due})/100)+{Manual Rate})
)

S
Forum|alt.badge.img+2
  • shasixz
  • Author
  • Participating Frequently
  • January 12, 2024

Any help, please?

Terms & ConditionsAccessibility statement