Calculating Time Values

server54451

Hi Team,

I am new to the community but have been a SS user since 2012. In some reagrds I am still a learner but in my process ability I am building amazing stuff. Let me just say it has transformed my world and clients I work with!  I am getting to work with some real complicated setups and trying to translate that into a workable solution for my clients.

The time calculation formula is what I need to know more details about... I have read several community posts and and the comments seem so easy but when I try it myself I am just not winning... So i have seen the massive formula of over two thousand long and I saw another one that was shorter...  Tried both and not sure if I need to change something in the formula... 

I am not winning that is the bottom line... I have added a screen shot if someone can be a bit more accurate with how to pop in that formula to make it work please.

PS: This is the formula Travis posted on the community but when I copy and paste that into a the line, it does not work....

To use it, copy and paste it into a text editor and use FIND & REPLACE to add your cell references to it. 

=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

 

Andrée Starå

Hi,

Here's my formula that is working great. You maybe need to change the ; to , with find and replace.

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

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

server54451

Andree,

Thank you for replying, can you perhaps send me your email that I can share the file with you? I did as you said and not coming right.  Not sure what I am doing wrong else.

My email is lucas@africapowersolutions.com