i need a formula for the number of days between start date and end date after subtracting whole mont

[Gokul1]

i need a formula for the number of days between start_date and end_date after subtracting whole months and also the number of whole months between start_date and end_date.

[WillowMobileSys]

---

@Gokul1 wrote:

formula for the number of days between start_date and end_date after subtracting whole months

---

Please explain more about what you mean by this.  The "after subtracting whole months" part could be interpreted in many ways.

Are there any constraints on the dates that can help simplify the expression?  for example, "the dates are always in the same year"  or "there dates are never more than 1 year apart"?

[Gokul1]

sorry about the late response, no such constraints can be used. i want a formula for a function similar to the one in google sheets. DATEDIF("2/16/1969", "7/24/1969", "md") and DATEDIF("2/16/1969", "7/24/1969", "m").

[Sirfyaad · Accepted solution]

To calculate the number of whole months and the remaining days between two dates.

Numer of Whole Months:

YEAR([end_date]) * 12 + MONTH([end_date]) - (YEAR([start_date]) * 12 + MONTH([start_date]))

Number of Remaining Days:

DAY([end_date]) - DAY([start_date])

1. In your AppSheet app, go to the table or column where you want to calculate the number of whole months or remaining days.
2. Create a new column or edit an existing one to store the result.
3. Set the column type to "Virtual" if you only want to display the calculated value without storing it in the database. Otherwise, set it to an appropriate data type (e.g., "Number" or "Text").
4. In the formula editor for the virtual column or the appropriate column property, enter the respective formula mentioned above.

Make sure to replace [start_date] and [end_date] with the actual column names or expressions that represent the start and end dates.

[Izzat_Safrah]

HOUR([Start Date]-[End Date])/24

or

HOUR(TODAY()-[EXPIRY DATE])/24