topic formula not working for end date as null in App Development

formula not working for end date as null

ritumishra01 — Tue, 22 Aug 2023 09:26:04 GMT

below formula is not working at data load editor

if((len (mi_end) <=0 ),date(Today()) - date(mi_start) ,mi_end - mi_start ) as mi_duration_new1,

Re: formula not working for end date as null

Or — Tue, 22 Aug 2023 09:43:06 GMT

Not sure what you mean by "not working". You'll have to elaborate on this one.

Len() can't be less than zero, and using len() on a date is kind of odd.

Date(mi_start) shouldn't be necessary, mi_start should be enough.

Coalesce(mi_end,Today()) - mi_start sounds like it might work here.

Re: formula not working for end date as null

ritumishra01 — Tue, 22 Aug 2023 09:50:46 GMT

if((len (mi_end) <=0 ),date(Today()) - date(mi_start) ,mi_end - mi_start ) as mi_duration_new1,

actually i am trying this formula at back end , where i am trying to calculate the time duration .

if mi_end value is not present or is null in that case it should take today's date and calculate no of days .

like if submitted date is 2/2/2023 and request is still in submitted state so it should give me no of days it is in submitted state today() - 2/2/2023 , where 2/2/203 is the start end and toady is the end date .

what formula i can apply for this logic implementation?

Re: formula not working for end date as null

Or — Tue, 22 Aug 2023 09:59:54 GMT

Have you tried the one I suggested?

Re: formula not working for end date as null

ritumishra01 — Tue, 22 Aug 2023 10:49:12 GMT

it did not work

Re: formula not working for end date as null

Or — Tue, 22 Aug 2023 13:42:44 GMT

That's not specific enough to work with, I'm afraid...

That formula should work if the values are either null or a valid date, and the fields are recognized as dates. I'd suggest confirming that this is the case for your data.

Re: formula not working for end date as null

marcus_sommer — Wed, 23 Aug 2023 11:23:14 GMT

Just using the following should be enough:

alt(mi_end, today()) - mi_start as mi_duration_new1