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
Solved! Go to 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
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
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.
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
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
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
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.
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.
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |