I need to calculate the length of time betwee...

I need to calculate the length of time between two dates.

Start date and closed date.

The result will show the length of time it took to carry out an action in days.

Would it be best to add a column in the table or add a virtual column in the app, also what would the expression needed be?

Thanks.

I have used this formula which has worked but not returned the value in days.

=[Date Raised]-[Closed Date]

@Nick_Parsons1 weren’t your start and closed dates Datetime column? (If I remember correctly) If so, enclose your columns with Date function

Date([Start]) - Date([Closed])

Any further ideas with this none, I just cannot get it to work…

Hi @Nick_Parsons1 try

HOUR([Closed]-[Start])/24 make sure your column type is number.

@Lynn Hi, thanks for the reply, its strange, it seems to work giving the correct duration,

but for rows that do not contain a date as yet (blank) as the issue is not closed, the returned value for example is like this?

-17,792

Hi I think that is normal behaviour for this expression.

@Nick_Parsons1 eval the formula like this:

=IFS( AND(ISNOTBLANK([Start]),ISNOTBLANK([Closed])),HOUR([Closed]-[Start])/24, TRUE,NUMBER(""))

1 Like

@Levent_KULACOGLU BRILLIANT…works perfectly! thank you Levent.

Additionally, I would like to add text to the result in the formula, would this be possible.

So, instead of the result being “4” I would like it to be “Days - 4”

I would also like to replicate this but instead of being between the start and finish date, I would like it to be a duration between start date and today()? With the same result, would the formula you supplied do this with a minor change?

@Nick_Parsons1 It should work ok using TODAY That is what i use in my formula. Also if you want to add text create another column or VC and use concatenate.

@Lynn Hi Lynn, thank you for replying, I have tried but I think I may be getting the syntax wrong.

Where would I add “Today” into this formula? I assume I would replace Closed Date and remove the square bracket but that does not work?

=IFS( AND(ISNOTBLANK([Date Raised]),ISNOTBLANK([Closed Date])),HOUR([Closed Date]-[Date Raised])/24, TRUE,NUMBER(""))

@Nick_Parsons1 eval the formula like this:

=IFS( ISNOTBLANK([Start]),CONCATENATE(“Days - “,HOUR(NOW()-[Start])/24), TRUE,CONCATENATE(””))

@Levent_KULACOGLU Ok that seemed to work but the result instead if showing in days, shows this?

-24:00:00

@Nick_Parsons1

(Date([Closed]) - Date([Start]))/24

@Levent_KULACOGLU Oops…Error.

Date arithmetic expression (DATE([Closed Date])/24 has an invalid operator

Date(([Date Raised]) - Date([Closed Date])/24

@Levent_KULACOGLU I have tried additional parenthesis etc, but I keep getting the error, invalid Operator, I assume the / ?

@Nick_Parsons1

Can you try HOUR(Date([Closed]) - Date([Start]))/24

@Levent_KULACOGLU The expression is valid but its result type ‘Number’ is not one of the expected types: Date

Is this the column Type?

What’s your column’s type that you intend to make this calculation @Nick_Parsons1 ?

@Levent_KULACOGLU I have created a virtual column that I would like to display a duration in days.

So, for example, this action is “12” days old…

Duration = 12