Calculate the time between two dates by days, hours and minutes

matt69051

Paul,

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.

Start------------Stop----------Hours

12:30 PM___1:30 PM___-11:00

12:30 PM___5:00 PM___-7:0-30

10:00 AM___3:00 PM___-7:00

I added the underscores to the data looks like data-ish.

 

Any thoughts?

 

Thanks in Advance,

Matt

Andrée Starå

Hi 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!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Matt,

 

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).

roy_ricky09

see this is what happens when day changes

roy_ricky09

see

ker9

@L@123

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!)

Paul Newcome

@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.

Paul Newcome

@ker9

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).

ker9

@Paul Newcome

Thank you! I will give your method a try.

remkin

Hey,

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.

Julie Barbeau

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)?