Change duration to number value

[Mic_L_Angelo]

I need help extracting the number of days from a duration value.

I’m using this formula

(TODAY()) - [Mod Start]
(Mod Start is a past date)

The expression give me a duration value (“1995.00:00:00”).
I’d like to give me the number of days value (“1995”)

Any help would be appreciated

[Marc_Dillon]

Posted a bit too hastily before, then went to test my reply (test after the reply, probably the wrong order…).
I don’t understand how your duration value has “1995.” in the front.

This article should still help. There are some rather complicated formulas at the bottom for getting years/months/days out of a duration

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

[Mic_L_Angelo]

the “1995” is the number of days. the value seems to be in DD:HH:MM:SS format. the values are two whole dates, so I just want the days.

Appsheet, frustratingly, doesn’t provide a simple way to do this.

I’ll take a look at the article and let you know.

Addendeum: I’ve actually already saw that article before I typed my request. Nothing in there about how to extract the days from the duration. So I need someone help me with a custom expression.

[Mic_L_Angelo · Accepted solution]

Actually, nope. @Marc_Dillon I stand corrected. It was buried in the article, but I found it.

HOUR(TODAY() - [Mod Start]) / 24

This just gives me the days

So I’m good with this one. thanks

[Marc_Dillon]

Oh, ok cool. That’s exactly what I typed in my first (deleted) respond, but I then tested it with the duration value you provided and it gave me 0. Hmmm

[Mic_L_Angelo]

I was getting those too when I converted today to a number

(NUMBER((TODAY()) - [Mod Start]) / 24)

seems keeping it as a duration value did the trick. It’s not obvious tho. But it worked so I guess there’s that

[Kirk_Masden]

Hi!  I'm just coming across this because I had a similar question and wound up doing the following:

ceiling(([Later datetime]-[Earlier datetime])/decimal(24))

I did it this way because I wanted any part of a 24-hour period to be considered one day.  Without the "decimal" and the "ceiling," anything under 24 hours (that is, any part of a day) was discarded.  There's probably a better way but this is what I came up with.

[Kirk_Masden]

After a bit more consideration, I changed my expression as follows:

hour(date([Later datetime])-date([Earlier datetime]))/24+1

This way, the expression counts the number of calendar days.  So, even if the period is just a few hours, if it starts late in one day and ends in the early morning hours the next, the result would be 2, which is what I wanted.