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โฆ
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:
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.
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
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
Open to ideas lol
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
Yeah, it really is painfulโฆ
Youโre welcome.
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.
A good idea, but the curation effort to keep it concise would be substantial.
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
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โ)
If I donโt have decimal is there a better way? If itโs only whole number hrs?
Iโm not 100% but I think you can just *1.0 and itโll force it to decimal for calcsโฆ
Affirmative
Thanks @MultiTech_Visions youโre one of my favorite Appsheepleโฆ Come on, thatโs pretty good slangโฆ
I mean can I avoid the *60 portions because I will never have part of a number. Numbers auto convert to decimal when doing math I believe.
If youโre never going to have a decimal portion of the hours, and you literally just need to โinjectโ the hours into the duration, you could do it this way:
NOW() - concatenate(left(โ000โ & [Hours], 3), โ:00:00โ)
Iโm thinking that would work (but I havenโt tested that)
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |