Day or months between two dates

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?

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

2 Likes

Perfect thanks that works

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
image

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.