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(""))
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?
@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
(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 / ?
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
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.
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |