Snippet help - calculate number of days in month from date

Questions and Help
1

Can someone help me with a formula that will calculate the number of days in a month based off the date picker?

I use this for pro-rating rent.

So my calculation is (montlyRent/daysInMonth)*(daysInMonth-moveInDay+1).

Right now I'm doing this in a stupid way where I have a selector that picks number of days in the month from 30,31,28. But I'd like to use the date selector for the exact move in date and calculate off of that.

Can someone help me with this? Similar to: Number of Days in a Month - Formulas in Excel and Google Sheets - Automate Excel

Thanks in advance for any tips!!!

2

Thanks for the interesting challenge :slight_smile: Try this:

You selected: {formdate: YYYY-MM-DD; name=moveindate}
Next month start date: {nextmonth={time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD; shift=+1M}}{=nextmonth}
This month start date: {startmonth={time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD}}{=startmonth}
Days left in this month: {leftdaysmonth=datetimediff(moveindate, nextmonth, "D")}{=leftdaysmonth} (inclusive of selected date)
Total days in this month: {totaldaysmonth=datetimediff(startmonth, nextmonth, "D")}{=totaldaysmonth}

1 Like
3

Super helpful! Thank you!!! Can you also tell me how to extract the move in day from the date picker? Just the DD selected.

4

Sure, have a look:

You selected: {formdate: YYYY-MM-DD; name=moveindate}
Move in day: {moveinday={time: DD; at={=moveindate}; pattern=YYYY-MM-DD}}{=moveinday}

1 Like
5

I promise last one. Sorry! I'm bad at these date formats.

Last date of this month (should be 11/30/2022)
Last date of next month (should be 12/31/2022)

6

Sure, no problem. You need to use the {time} command like so:

{time: required-format; at={=input-date}; pattern=input-format}

So, in this case:

You selected: {formdate: YYYY-MM-DD; name=moveindate}
Next month start date: {nextmonth={time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD; shift=+1M}}{=nextmonth}
Next month start date in your format: {time: MM/DD/YYYY; at={=nextmonth}; pattern=YYYY-MM-DD}

I hope that makes sense.

7

I was trying to get the last day of the month instead of the start date of the month. :slight_smile:

8

We can use a negative one day shift for that. Last day of this month = first date of next month - 1 day.

You selected: {formdate: YYYY-MM-DD; name=moveindate}

{getstartdate=(shift)->{time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD; shift=+{=shift}M};trim=right}
{getlastdate=(firstdate)->{time: MM/DD/YYYY; at={=firstdate}; pattern=YYYY-MM-DD; shift=-1D};trim=yes}

Start of next month: {=nextmonthstart}{nextmonthstart=getstartdate(1)}
This month last date: {=getlastdate(nextmonthstart)} (subtract one day from next month start)

Start of next next month: {=nextnextmonthstart} {nextnextmonthstart=getstartdate(2)}
Next month last date: {=getlastdate(nextnextmonthstart)} (subtract one day from next next month start)

1 Like
9

Duh! That makes sense. Brain fry. Thanks so much!!!!

1 Like
10

Here is my full snippet finally working.

Thanks @Gaurang_Tandon for helping me streamline some of the issues that were bugging me!!!

Some of the logic:

  • Calculate pro-rated rent for remaining days in the month
    -- If the move-in date is >= 25th then calculate including next month's rent as well
  • Calculate pro-rated pet rent into proration
  • Calculate pet deposit based on number of pets and include in move-in funds
  • Subtract pro-rated rent of $400 from reservation binder fee of $550
  • Calculate monthly rent + pet rent based on number of pets
  • Calculate total move in costs including security deposit and pet fees and pro-rated rent and reservation binder fee
  • Calculate remaining balance after reservation binder fee has been paid

{note}
FILL IN FIELDS TO CALCULATE
Monthly rent: ${formtext: name=mr; default=; cols=10}
Move-in Date: {formdate: YYYY-MM-DD; name=moveindate}
Pets:{formmenu: default=0; 1; 2; 3; name=petnum; cols=5}
Concession: {formmenu: default=None; First full month of rent free; $500 off first full month of rent; $500 first responder/military discount; name=concession}
{endnote: trim=right}

ESTIMATED MOVE IN FUNDS*
Reservation binder fee: $550
$400 goes towards pro-rate, $150 admin fee
Security deposit (one month's rent): ${formtext: name=mr; default=}
Pro-rated rent from {frommid={time: M/D/YYYY; at={=moveindate}; pattern=YYYY-MM-DD}}{=frommid} until {if: mid <= 24}{=getlastdate(nextmonthstart)} {else} {=getlastdate(nextnextmonthstart)}{endif}: {if: mid < 25}{=(mr/dim)(dim-mid+1) +(petnum * 35 / dim) * (dim - mid +1) - 400; format=$,.2f}{else}{=(mr/dim)(dim-mid+1) +(petnum * 35 / dim) * (dim - mid +1) + mr + (petnum *35) - 400; format=$,.2f}{endif}

  • minus $400 from reservation binder
    {if: mid >= 25} * including following month's rent if move-in is the 25th or after{endif}
    {if: petnum > 0} * including pet rent for {formtext: name=petnum} pet(s) {endif}
    {if: petnum >= 1}Non-refundable pet fee: {if: petnum == 0}n/a{elseif: petnum == 1; trim=yes}$250.00{elseif: petnum == 2; trim=yes} $350.00
  • $250 first pet, $100 second pet{elseif: petnum == 3; trim=yes}$450.00
  • $250 first pet, $100 for second and third pet{endif} {endif}
    Total due after reservation binder fee (minus $550): {if: mid < 25}{=(mr/dim)(dim-mid+1) +(petnum * 35 / dim) * (dim - mid +1) + x + mr - 400; format=$,.2f}{else}{=(mr/dim)(dim-mid+1) +(petnum * 35 / dim) * (dim - mid +1) + (mr2) + (petnum 35) + x - 400; format=$,.2f}{endif}
    All in total including reservation binder fee (plus $550): {if: mid <= 24}{=(mr/dim)    (dim-mid+1) +(petnum * 35 / dim) * (dim - mid +1) + x + mr + 550 - 400; format=$,.2f}{else}{=(mr    2)+(mr/dim)*(dim-mid+1) +(petnum * 35 / dim) * (dim - mid +1) + (petnum *35) + x + 550 - 400; format=$,.2f}{endif}

Other details
Monthly rent: ${=mr + (petnum * 35)} {if: petnum > 0}(includes rent for {=petnum} pets at {=petnum * 35; format=$,.2f}/per month){endif}
Move-in concession: {=concession}

  • Please note that this is just an estimate. Please always refer to your reservation binder email for actual amounts.

{mid={time: DD; at={=moveindate}; pattern=YYYY-MM-DD}}
{dim=datetimediff(startmonth, nextmonth, "D")}
{nextmonth={time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD; shift=+1M}}
{startmonth={time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD}}
{leftdaysmonth=datetimediff(moveindate, nextmonth, "D")}
{getstartdate=(shift)->{time: YYYY-MM-01; at={=moveindate}; pattern=YYYY-MM-DD; shift=+{=shift}M}; trim=right}
{getlastdate=(firstdate)->{time: MM/DD/YYYY; at={=firstdate}; pattern=YYYY-MM-DD; shift=-1D}; trim=yes}
{nextmonthstart=getstartdate(1)}
{nextnextmonthstart=getstartdate(2)}
{if: petnum == 0}{x=0}{endif}
{if: petnum == 1}{x=250}{endif}
{if: petnum == 2}{x=350}{endif}
{if: petnum == 3}{x=450}{endif}

3 Likes