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

LouiseM

Hi,

I am trying to implement the following formula from my excel spreadsheet into my smartsheet:

=INT(O2-Q2)&" Days "&HOUR(MOD(O2-Q2,1))&" Hour "&MINUTE(MOD(O2-Q2,1))&" Minutes"

Basically, i need to calculate the time between two columns (date received) and (date documents issued) in days, hours and minutes.

Cell 'O2' being date issued and 'Q2' being date received.

For example: we had a new business request in at say 10:30 on 05/05/2018 we sent out the documents at 12:45 on 10/05/2018. In my new column, the formula would calculate that the time between these two is 5 days, 2 hours and 15 minutes.

So I have a column for the time and date the business came in.

And a column for the time and date the documents were sent out.

I have tried to paste the formula in, and I worked out that you need to use the name of the column in square brackets '[XX]' , instead of cell letters and numbers 'A3'...but everything else i have tried just keeps coming up with '#unparseable'.

I hope that makes sense, can anyone help?

Bryana_S

In my experiences, Smart Sheets does not know how to calculate time. I would love to know if someone else has found a way to make it work!

Paul Newcome

You can write a formula taking into account am, pm, and the " : ", but it is EXTREMELY complicated and very easy to break. The only way I have been able to find is to use 24 hour time and use MID functions to pull the two hour digits and the two minute digits, factor in /60 in the minutes section, calculate your times, then put it all back together. Again though... Rather complicated and easily breakable (not as easily as the other one though). If you are interested I can post it here, but I can't lie... It's a pain in the rear...

Chris McKay

And it's the reason we're dropping Smartsheet in the very near future. Once again, we've had an update that along with welcome bug fixes, introduces trivial features (Active faces in menu bar, Forms menu etc.) while shortfalls like the ability to deal with time values or accurate dates based on location have been hanging around for more than 2 years.

Smartsheet.... sort out your development priorities in line with what we're asking for. This community cannot be too far removed from what your other customer feedback streams are telling you. Nobody can be happy that the product handles dates/times so poorly. Especially when we're paying 10s of thousands for a PPM tool that should have this functionality straight out of the box.

LouiseM

Okay, thank you that's really helpful, although it sounds like your expertise in formulas is far more advanced than my own!

If you could post it, i can give it a go and try and work out how to do it!

It seems this is not going to be as easy as i thought...

Paul Newcome

This is the one for the 12 hour clock. I still need to dig through my notes to find the 24 hour one.

 

=IFERROR(INT((((IF(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1) = "12", IF(OR(FIND("a", [Finish Time]1) > 0, FIND("p", [Finish Time]1) > 0), 0, 12), VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1))) + IF(FIND("p", [Finish Time]1) > 0, 12)) * 60 + VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2))) - ((IF(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1) = "12", IF(OR(FIND("a", [Start Time]1) > 0, FIND("p", [Start Time]1) > 0), 0, 12), VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1))) + IF(FIND("p", [Start Time]1) > 0, 12)) * 60 + VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1) = "12", IF(OR(FIND("a", [Finish Time]1) > 0, FIND("p", [Finish Time]1) > 0), 0, 12), VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1))) + IF(FIND("p", [Finish Time]1) > 0, 12)) * 60 + VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2))) - ((IF(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1) = "12", IF(OR(FIND("a", [Start Time]1) > 0, FIND("p", [Start Time]1) > 0), 0, 12), VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1))) + IF(FIND("p", [Start Time]1) > 0, 12)) * 60 + VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)))) - INT((((IF(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1) = "12", IF(OR(FIND("a", [Finish Time]1) > 0, FIND("p", [Finish Time]1) > 0), 0, 12), VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1))) + IF(FIND("p", [Finish Time]1) > 0, 12)) * 60 + VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2))) - ((IF(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1) = "12", IF(OR(FIND("a", [Start Time]1) > 0, FIND("p", [Start Time]1) > 0), 0, 12), VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1))) + IF(FIND("p", [Start Time]1) > 0, 12)) * 60 + VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1) = "12", IF(OR(FIND("a", [Finish Time]1) > 0, FIND("p", [Finish Time]1) > 0), 0, 12), VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1))) + IF(FIND("p", [Finish Time]1) > 0, 12)) * 60 + VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2))) - ((IF(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1) = "12", IF(OR(FIND("a", [Start Time]1) > 0, FIND("p", [Start Time]1) > 0), 0, 12), VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1))) + IF(FIND("p", [Start Time]1) > 0, 12)) * 60 + VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)))) - INT((((IF(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1) = "12", IF(OR(FIND("a", [Finish Time]1) > 0, FIND("p", [Finish Time]1) > 0), 0, 12), VALUE(LEFT([Finish Time]1, FIND(":", [Finish Time]1) - 1))) + IF(FIND("p", [Finish Time]1) > 0, 12)) * 60 + VALUE(MID([Finish Time]1, FIND(":", [Finish Time]1) + 1, 2))) - ((IF(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1) = "12", IF(OR(FIND("a", [Start Time]1) > 0, FIND("p", [Start Time]1) > 0), 0, 12), VALUE(LEFT([Start Time]1, FIND(":", [Start Time]1) - 1))) + IF(FIND("p", [Start Time]1) > 0, 12)) * 60 + VALUE(MID([Start Time]1, FIND(":", [Start Time]1) + 1, 2)))) / 60) * 60), 0)

 

Of course [Start Time]1 and [Finish Time]1 will be replaced with your column and row references. If you replace the 1 with @row, things will be a little more flexible. The other thing you need to watch with this one... As is, it is 3,179 characters long already and has 90 column references.

Each cell can only hold 4,000 characters including spaces. Depending on your column names you could exceed that rather quickly.

 

Side note: Smartsheet does NOT support the MOD function.

LouiseM

Wow.

Thank you for your help Paul, really appreciate it. I will give this formula a try and see what happens...I think I'll just have to add (and then hide) a few new columns with shorter names to fit under the 4,000 character threshold, as i was not aware of this!

I think i might just write off a few hours to sit down and work this out, I'll try the 12 hour one and if that doesn't work then i probably wont need the 24 hour one, hopefully it all goes smoothly and works perfectly first time!

Andrée Starå

Hi,

This is the one I use for 24 hours.

=IFERROR(INT((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) / 60) + ":" + IF(((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) - INT((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) / 60) * 60) < 10; "0") + ((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) - INT((((IF(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Stop 1]@row) > 0; FIND("p"; [Stop 1]@row) > 0); 0; 12); VALUE(LEFT([Stop 1]@row; FIND(":"; [Stop 1]@row) - 1))) + IF(FIND("p"; [Stop 1]@row) > 0; 12)) * 60 + VALUE(MID([Stop 1]@row; FIND(":"; [Stop 1]@row) + 1; 2))) - ((IF(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1) = "12"; IF(OR(FIND("a"; [Start 1]@row) > 0; FIND("p"; [Start 1]@row) > 0); 0; 12); VALUE(LEFT([Start 1]@row; FIND(":"; [Start 1]@row) - 1))) + IF(FIND("p"; [Start 1]@row) > 0; 12)) * 60 + VALUE(MID([Start 1]@row; FIND(":"; [Start 1]@row) + 1; 2)))) / 60) * 60);0)

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Paul Newcome

This looks like a 12 hour clock with a.m. and p.m.? It just seems WAY more complicated than a 24 hour clock would need, or am I reading wrong (very possible)?

 

Here is what I use to determine the duration of tasks based on a 4 digit 24 hour clock (0000 - 2359):

=(SUM(VALUE(MID([Finish Time]@row, 1, 2)) - VALUE(MID([Start Time]@row, 1, 2)), "", SUM(VALUE(MID([Finish Time]@row, 3, 2)) - VALUE(MID([Start Time]@row, 3, 2))) / 60))

Paul Newcome

By the way... That wicked long formula I posted above is for TIME ONLY. It does not take into account dates. Considering the current length of it you may need to create a couple of hidden support columns for the dates then write up a quick JOIN formula to combine them.

Andrée Starå

It could probably be possible to make it better, but the important part is that it works.

Best,

Andrée

L_123

https://community.smartsheet.com/discussion/time-formula-drop-down-selection

 

https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da

Paul Newcome

Well look at YOU Mr. Smartypants... Lol.

 

I wasn't able to "Save As" on your sheet, so I built one like yours and copy pasted the formulas. I ended up getting an Unparseable error though.

L_123

maybe an issue with caps?

L_123

You put a space in time start

 

ha i wasn't very user friendly with the column names was I. oh well. was more worried about the formulas

Paul Newcome

Haha. No worries. The formulas are the tricky part. Fixed my column name, and it works like a charm. I will DEFINITELY be saving it as a template. Much obliged, Sir.

 

I hope the original poster sees this! Hahaha