New Formula Please: DatedIF()

[MultiTech]

Hey everyone

---

Let’s say I want to show length of time that something has been whatever,

and I want to show that in the following format:

• 1 years, 4 months, 16 days

In excel, we have =DATEDIF() that we can use - which results in a nice and efficient formula:

=DATEDIF(G6,H6,“y”)&" years, "
&DATEDIF(G6,H6,“ym”)&" months, "
&H6-DATE(YEAR(H6),MONTH(H6),1)&" days"

But if I want to accomplish this in AppSheet… well, just take a look at this thread.

Convert Birthdate to Current Age in Years/Months format Tips & Tricks 💡

Calculating the current Age based on a date-of-birth column: [48%20AM] Formula FLOOR(HOUR(TODAY()-[DOB])/365/24)&" yr "& FLOOR(MOD((HOUR(TODAY()-[DOB])/24),365)*12/365) & " mo" Line 1 HOUR(TODAY()-[DOB]) returns the number of Hours in the duration /365/24 converts hours to years, and FLOOR gives the number of full years, dropping the decimal. Line 2 (HOUR(TODAY()-[DOB])/24) returns the number of days in the duration MOD((...),365) finds the remainder in days, after removing the …

It seems to me all this would be simpler, along with some other date-related display troubles, from the addition of the DatedIF() formula.

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

---

As always, thanks for considering.

[HBT]

+1 for this request

and it also needs some formulas like a date formula that calculates the difference between two dates, which needs to be done in one step. I will add the necessary formulas under this request.

[Steve]

HBT:

it also needs some formulas like a date formula that calculates the difference between two dates, which needs to be done in one step.

Why? What’s wrong with the current way?

(HOUR(date2 - date1) / 24)

[HBT]

Dear @Steve , thank you very much for your answer.

I know the use of the above formula.

When I first needed to calculate the number of days between two dates, I learned this use on the form and by reading your articles. The form contains examples of this use. I know that.

Hi, How can I minus two Dates and get a numb... Questions

Are you looking for number of days’ difference between two dates ? If so,then please try the following expression HOUR ( [Date1] - [Date2] ) / 24

But this is a bit of a forced method.

When Google Spreadsheets Excel came out as a competitor, it introduced many functions of Excel as they were.

So people switched from Excel to Google Spreadsheets without learning anything new.

Many things that are known to be done in Excel and SQL can also be done to AppSheet.

For example, the function between two dates is generally used as follows.

Sql: DATEDIFF (DAY, ‘19900101’, ‘20201229’)
Delphi: DaysBetween (D1, D2)
Excel 2013 and later: Days (D1, D2) or DATEDIF (Start_date, End_date, “D”)
PHP: date_diff ($ datetime1, $ datetime2);
Python: Variable = d1 - d2
Google Spreadsheet: Minus (D1-D2) or DATEDIF (d1, d2, ”D”)
or other programming languages, using similar functions.

I only see Powers app as a competitor to Appsheet. Others are just babies.

Appsheet, If Microsoft is going to be a competitor, it should definitely standardize its functions. So let’s use these functions based on our previous knowledge and feelings. Let’s not do research. Let’s not learn a new method.

You may not think like me.

I hope that what I mean is understood.

[hkeziah]

I'm new to appsheet, and really getting frustrated.

For instance, =DATEDIF(startdate, enddate, "y") works in a Google Sheet as a formula. But NOT in appsheet.

Why isn't there parity? I shouldn't have to enter a complicated formula in appsheet when in a sheet it's one simple function.