Help

How to calculate number of months are included within a quarter based in check in and check out day

Topic Labels: Formulas
2425 2
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
juststay
4 - Data Explorer
4 - Data Explorer
See Solution in Thread

‎Nov 13, 2023 03:23 AM

Dear Community, 

hope someone can help me out with this!

The formula below indicates based on {Check in date} and {Check out date} how many months a booking of a rental apartment is actually in the current quarter. However in this formula, when the check in date is higher then the 17th of the month in gives one month less. So lets say check in date 17th of October and check out on the last day of December, it gives 3 months. Anything above the formula gives 2 months. However, this is not correct. For any date above the 17th of the month it should also give 3 months as the booking falls into 3 months of the quarter. Obviously if a new month start then it should give 2 months so 1/11 till 31/12 is 2 months. Can someone please help me write a correct formula for this?

The formula below was created by Airtable support, but incorrect and they did not want to help me further! Thank you so much!!!

ROUND(
IF({Check out date}<=DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),0,

IF(AND({Check in date}<=DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),{Check out date}<=DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day')),
DATETIME_DIFF({Check out date},DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),'days')/30,

IF(AND({Check in date}>DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')), {Check out date}<=DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day')),
DATETIME_DIFF({Check out date},{Check in date},'days')/30,

IF(AND({Check in date}<=DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),{Check out date}>DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day')),
DATETIME_DIFF(DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day'),DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),'days')/30,

IF(AND({Check in date}>DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),{Check out date}>DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day'),
{Check in date}<=DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day')),
DATETIME_DIFF(DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(IF(MOD(MONTH(TODAY()), 3) = 0, MONTH(TODAY()) - 2 &'/01/' &YEAR(TODAY()), FLOOR(MONTH(TODAY()) / 3) * 3 + 1&'/01/' &YEAR(TODAY())),'MM/DD/YYYY')),3,'months'),-1,'day'),{Check in date},'days')/30,0

))))))
0 Kudos
Reply
2 Replies 2
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer
See Solution in Thread

‎Nov 13, 2023 03:52 PM

Hey @juststay sounds like you've got a rounding problem. If the guest stays less than half the month it rounds down, and if they stay more than half the month it rounds up. But you want it to only round up (even 1 day out of 30 counts as a full month)

Try changing your ROUND function to ROUNUP and give a precision of 0 (round to full integer). 

That is a monstrously large formula block so trying to debug (and maintain) will likely be a nightmare. ChatGPT might help to untangle some of the threads. 

Good luck. 

0 Kudos
Reply
juststay
4 - Data Explorer
4 - Data Explorer
In response to Arthur_Tutt
See Solution in Thread

‎Nov 16, 2023 06:10 AM

Dear Arthur,

Super thanks for your reply. I tried asking chat gpt, but it is having a really really hard time. If with a small section of the formula. Chat GPT gave me this formula below to check based on the check in date how much months fall into the current quarter. Its completely wrong, what is the oversight or the mistake chat gpt makes? As an example chat gpt should give me 2 months if i put in check in date: 20/10/2023 and check out date: 30/11/2023. Or 1 month if check in is 7/10/2023 and check out 11/10/2023. However it doesnt....

Can you help me please resolve this? 😞 Thank you so much!!


IF(
AND(
{Check in date} >= DATETIME_PARSE(DATETIME_FORMAT(TODAY(), 'YYYY-MM') & '-01'),
{Check in date} <= DATEADD(DATEADD(DATETIME_PARSE(DATETIME_FORMAT(TODAY(), 'YYYY-MM') & '-01'), 3, 'months'), -1, 'days')
),
1,
0
)





0 Kudos
Reply
Copyright © 2024 Khoros, LLC