Day or months between two dates

AppSheet AppSheet Q&A
1

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?

2

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

5 Likes
3

Perfect thanks that works

4

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

2X_4_49024046e460c73ad137b78ef37e9d74c0177306.png

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.

They even have a downloadable excel document.

Are you able to replicate this expression in appsheet.

1 Like
5

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

1 Like