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

expressions
(Nick Parsons) #1

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.

(Nick Parsons) #2

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

=[Date Raised]-[Closed Date]

(Levent KULAÇOĞLU) #3

@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])

(Nick Parsons) #4

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

#5

Hi @Nick_Parsons1 try

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

(Nick Parsons) #6

@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

#7

Hi I think that is normal behaviour for this expression.

(Levent KULAÇOĞLU) #8

@Nick_Parsons1 eval the formula like this:

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

(Nick Parsons) #9

@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?

#10

@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.

(Nick Parsons) #11

@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(""))

(Levent KULAÇOĞLU) #12

@Nick_Parsons1 eval the formula like this:

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

(Nick Parsons) #13

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

-24:00:00

(Levent KULAÇOĞLU) #14

@Nick_Parsons1

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

(Nick Parsons) #15

@Levent_KULACOGLU Oops…Error.

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

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

(Nick Parsons) #16

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

(Levent KULAÇOĞLU) #17

@Nick_Parsons1

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

(Nick Parsons) #18

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

Is this the column Type?

(Levent KULAÇOĞLU) #19

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

(Nick Parsons) #20

@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