Not sure if you're still following this thread...but.
You're formula doesn't seem to be working for me. I have dropdowns for time in 15 minute increments.
I'm getting some interesting results.
12:30 PM___1:30 PM___-11:0012:30 PM___5:00 PM___-7:0-3010:00 AM___3:00 PM___-7:00
I added the underscores to the data looks like data-ish.
Thanks in Advance,Matt
Can you share the sheet or a copy if it contains sensitive information? I'd be happy to take a look.
Have a fantastic week!
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
My formula is for a 24 hour clock without AM or PM. It is made to look for 1300 being 1:00 PM. 1:00 would be 1:00 AM.
The long one for the 12 hour clock I haven't tested fully. It is so long that if you are not careful with your column names, it will exceed the 4,000 character per cell limit.
I honestly had forgotten about this post and never updated it once I figured out a different workaround. I'll dig through my notes and old posts and see if I can find something for you. I am pretty sure that Andree and I had discussed this in another post more recently (maybe in the past month or two).
see this is what happens when day changes
I love your solution!
Unfortunately, I'm running into a problem. I'm using your formulas from "AutoColumn Min Between" and "Auto Column Time Down", which worked until the (system) Create time was 12:44 PM and (system) Modified time is 1:45 PM (both on the same day).
The minutes between came out as -659
When I use the manual portion, it comes out correctly at 61 minutes.
(Awesome method to handle time, great job!)
@ker9 It sounds like it converted the 12 to 00 (midnight instead of noon). I have a few time solutions floating around here in the Community. I will see if I can dig up one of the solutions that uses the System columns. I know I have it somewhere. I just gotta find it.
HERE is a published sheet that uses a couple of helper columns. First we convert the Created time into a numerical value so that (for example) 9:30 AM = 9.5 and 9:30 PM = 21.5. Then we do the same for the Modified time, but we also add 24 (hours) for every day that the Modified date is greater than the Created date.
Then we subtract the Created Number from the Modified Number to give you the duration in a numerical value that can be used in future calculations such as SUMIFS and whatnot.
Finally I included a [Display Duration] column that converts the duration back into a hh:mm format for display. This is a text string and cannot be used for numerical calculations (that's what the CalcDuration column is for).
Thank you! I will give your method a try.
Want to hear something crazy.
I made it work I took the second timestamp and removed the first timestamp. I found the error was a ratio of 2.48 so I applied that to the formula and it worked.
I honestly dont beleive it its the first win Ive had battling smarthseet limitations. And trust me Ive come across them all.
I hope this helps someone.
Hi @Paul Newcome ,
I have a question for you. I use your formula and helper columns. Problem is everytime I save my sheet, modification time column is changing, as if the sheet is recalculating duration and the automatic column of modification time think it's a change in the row. Can you help me (see column Date & Heure confirmation commande ou soumission)?
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
©2022. All Rights Reserved Smartsheet Inc.