I have a decimal column [hours], anywhere from 0 hrs to 5 million hrs and I want to return a datetime, taking NOW() - [hours]
[hours] is currently decimal type with two digits… 1.73 hours…
Help, my brain is fried!
NOW() - ([hours] / 24)
No this is wrong…
But it feels so right!
I hate date and time math…
2 Likes
Same
Make an intermediate VC, Duration type. Build the Duration string manually from the decimal value
“00” & floor([hours]) & “:” & ([hours] - (floor([hours])) * 100) & “:00”
Then do NOW() - [intermediate vc]
??
Thus my suggestion of a DURATION() expression in:
[We have NUMBER(), DECIMAL()... I need PRICE()](https://community.appsheet.com/t/we-have-number-decimal-i-need-price/22697) Questions
We have the following functions available to convert data into a specific type: [image] [image] [image] We also have things like: LIST() LATLONG() LAT() LONG() But we’re missing: PRICE() I have a need to force something back into a price so I can use it in math. Thanks for considering!
3 Likes
That would work, yes; but what I’m trying to do is get away from having to create specific temporary variable holding columns… ones that are only used in a very specific situation.
I’d like to get rid of those and instead cascade all of those into a single field that I can then pull the data out and force it into a specific type. There is only ever one temporary variable present at a time, it’s just that they are of different types and in order to work with them certain concessions must be made.
if I have a single temporary variable, but that temporary variable could take the form of a number, a price, a decimal, a duration; in order for me to work with any of these, they each used to require their own individual column type - but since we can convert things to a specific type, I can throw them all inside a single text column and then format that data whenever I use it using a type forcing formula.
Marc_Dillon:
“00” & floor([hours]) & “:” & ([hours] - (floor([hours])) * 100) & “:00”
Well, that was harder than it needed to be… here is where I ended up
NOW()-
((TIME(
CONCATENATE(
MOD(FLOOR([target_drtn_hr_cnt]), 24),
“:”,
(MOD(((60 * 60) * [target_drtn_hr_cnt]), (60 * 60)) / 60),
“:”,
MOD(((60 * 60) * [target_drtn_hr_cnt]), 60)
)
)
- “00:00:00”
) + (FLOOR([target_drtn_hr_cnt] / 24) * 24))
It’s in this help article: DOH
7 Likes
Oohhh, TIME() !!
I guess that’s my DURATION() expression I was asking for?
That’s what one would call, “the long way around the barn”
a beefy expression if I ever saw one
2 Likes
Open to ideas lol
1 Like
Honestly, I think that’s the best one can do given the tools available… Times/dates are literally the worst.
One could say Time has always been the enemy
2 Likes
Yeah, it really is painful…
Grant_Stead:
It’s in this help article
You’re welcome.
2 Likes
I love seeing how you’ve gotten in and are helping create value…
It would be really cool if there was an arena on help.appsheet.com that had just a ton of examples…
It’s always interesting to see the problems people have, and how they solve them.
1 Like
A good idea, but the curation effort to keep it concise would be substantial.
1 Like
Unless the help can be meta tagged… Then yes…
Is this formula honestly the best/only method to make durations out of numbers in hrs
1 Like
As far as I know… LOL
The best way that i’ve thought to correct this is to allow a Unit Of Measure to be applied to number/decimal… (That would also let you see it as a label in certain places of the app…) or an expression with arguments for hours, minutes, seconds, days, weeks, months, years, etc… like DURATION([column_name], “Hours”)
1 Like
If I don’t have decimal is there a better way? If it’s only whole number hrs?