Day or months between two dates

[Skip2MiLu]

I have list of product batches that have a Batch Creation and Batch Expiry Date as a Date column types.

I have created two virtual columns respectively:

1. [Batch Expiry] - [Batch Creation]
2. [Batch Expiry] - [Batch Creation] / 30,4167

I thought I could use the following expressions based on what I saw in other posts:

1. [Batch Expiry] vs [Batch Creation]
2. [Batch Expiry] vs [Batch Creation] / 30.4167

I tried using both Number and Decimal column type for the virtual columns but I recieved the following error respectively

The expression is valid but its result type ‘Duration’ is not one of the expected types: Decimal

I get the same for Number column type.

What am I missing here?

[Aleksi · Accepted solution]

The result for this [Batch Expiry] - [Batch Creation] is a duration like hours:minutes:seconds. If you want to have it as days, you should write it like TOTALHOURS([Batch Expiry] - [Batch Creation])/24

[Skip2MiLu]

Perfect thanks that works

[Skip2MiLu]

How complex would it be to design a expression that converted the duration between two dates as would a similar Datedif function in excel

See below excel fuction for caluculation of expression

This is the expression in excel:
=DATEDIF(G6,H6,“y”)&" years, "
&DATEDIF(G6,H6,“ym”)&" months, "
&H6-DATE(YEAR(H6),MONTH(H6),1)&" days"

Here is an overview of how the dateif formula works if you not familiar with it.

support.office.com

Calculate the difference between two dates

Calculate the number of days, months, or years between two dates using Excel functions. For example, you can calculate age in years, months and days. You can also calculate the number of days between another date and today. You can also calculate...

They even have a downloadable excel document.

Are you able to replicate this expression in appsheet.

[Frihardian_Janu]

Hello Michael, If you still waiting for the answer. Try this one:
Go to the setting of your column duration > Auto Compute > Spreadsheet Formula.
You can use this Formula :

IF(INDIRECT(“H”&ROW())="";"";DATEDIF(INDIRECT(“H”&ROW());TODAY();“Y”)&" Years, “&DATEDIF(INDIRECT(“H”&ROW());TODAY();“YM”)&” Month, “&DATEDIF(INDIRECT(“H”&ROW());TODAY();“MD”)&” Days")

Let me explain some part of the formula to you

• (“H”&ROW()) > “H” is your column alphabet of start duration on your spreadsheet document

Hope this will help you
Thanks