Skip to main content

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

J
Forum|alt.badge.img+1

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

))))))

2 replies

Arthur_Tutt
Forum|alt.badge.img+15
  • Arthur_Tutt
  • Inspiring
  • 118 replies
  • November 13, 2023

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. 

J
Forum|alt.badge.img+1
  • juststay
  • Author
  • New Participant
  • 1 reply
  • November 16, 2023
Arthur_Tutt wrote:

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. 


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
)





Reply


Most helpful members this week

Terms & Conditions