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.

0 26 4,312
26 REPLIES 26

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โ€ฆ

Lynn
Participant V

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

Lynn
Participant V

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

this formula worked great if i have [open date] [close date]

using this formulaโ€ฆis the a way to calculate days between 2 dates even if closed date is still empty?

Try:

(
  HOUR(
    IF(
      ISNOTBLANK([Closed]),
      DATE([Closed]),
      TODAY()
    )
    - DATE([Start])
  )
  / 24
)

life saverโ€ฆthis a long a show if less then to avoid the 18000 days if itโ€™s blank works great.

thank you again

Thank you for this WORKED GREAT!!!

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

Lynn
Participant V

@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

JPAlpano
Participant V

Hi Everyone,

I have successfully crated a Virtual column to calculate the days elapsed.
What I need is to have this recorded in the physical column.

Right now, I entered =[VC] in the App Formula of the physical column.

My question is "Will the data recorded in the sheet also update regularly even if nothing was edited for the record?"
Or will it only reflect the updated data in the sheet during sync, and ONLY if something was edited for that record?

That s the answer to your question.

Thanks @tsuji_koichi . This means I will have to put my formula in the Sheet then.

Top Labels in this Space