Home

Time formula please...

%3CLINGO-SUB%20id%3D%22lingo-sub-450793%22%20slang%3D%22en-US%22%3ETime%20formula%20please...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450793%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20help%20me%20with%20a%20time%20formula.%3C%2FP%3E%3CP%3ELeft%20hand%20columns%20are%20example%20of%20what%20I'm%20trying%20to%20achieve.%3C%2FP%3E%3CP%3EHW%20Oban%20changes%20daily%20(manually%20input)%20all%20other%20locations%20change%20automatically%20through%20formula.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E--%20Friday%2030%2F3%2F2018%3C%2FTD%3E%3CTD%3EHW%3C%2FTD%3E%3CTD%3E(BST)%20--%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E--%20Friday%2030%2F3%2F2018%3C%2FTD%3E%3CTD%3EHW%3C%2FTD%3E%3CTD%3E(BST)%20--%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHW%20Oban%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E18%3A21%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHW%20Oban%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E18%3A21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHW%20Crinan%3C%2FTD%3E%3CTD%3E(-0%3A47)%3C%2FTD%3E%3CTD%3E17%3A34%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHW%20Crinan%3C%2FTD%3E%3CTD%3E(-0%3A47)%3C%2FTD%3E%3CTD%3E%23%23%23%23%23%23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDorus%2C%20C'veckan%2C%20Cuan%20turn%20at%3C%2FTD%3E%3CTD%3E16%3A04%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDorus%2C%20C'veckan%2C%20Cuan%20turn%20at%20(-2%3A17%3A00)HW%20Oban%3C%2FTD%3E%3CTD%3E%23%23%23%23%23%23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECraobh%3C%2FTD%3E%3CTD%3E(-1%3A00)%3C%2FTD%3E%3CTD%3E17%3A21%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECraobh%3C%2FTD%3E%3CTD%3E(-1%3A00)%3C%2FTD%3E%3CTD%3E%23%23%23%23%23%23%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEasdale%3C%2FTD%3E%3CTD%3E(-0%3A25)%3C%2FTD%3E%3CTD%3E17%3A56%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EEasdale%3C%2FTD%3E%3CTD%3E(-0%3A25)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWest%20Loch%20Tarbert%3C%2FTD%3E%3CTD%3E(-3%3A30)%3C%2FTD%3E%3CTD%3E14%3A51%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EWest%20Loch%20Tarbert%3C%2FTD%3E%3CTD%3E(-3%3A30)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESmall%20Isles%2C%20Jura%3C%2FTD%3E%3CTD%3E(-3%3A00)%3C%2FTD%3E%3CTD%3E15%3A21%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESmall%20Isles%2C%20Jura%3C%2FTD%3E%3CTD%3E(-3%3A00)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDuart%20%26amp%3B%20Arisaig%3C%2FTD%3E%3CTD%3E(%2B0%3A15)%3C%2FTD%3E%3CTD%3E18%3A36%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDuart%20%26amp%3B%20Arisaig%3C%2FTD%3E%3CTD%3E(%2B0%3A15)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELochaline%3C%2FTD%3E%3CTD%3E(%2B0%3A12)%3C%2FTD%3E%3CTD%3E18%3A33%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ELochaline%3C%2FTD%3E%3CTD%3E(%2B0%3A12)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETobermory%3C%2FTD%3E%3CTD%3E(%2B0%3A16)%3C%2FTD%3E%3CTD%3E18%3A37%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETobermory%3C%2FTD%3E%3CTD%3E(%2B0%3A16)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELochalsh%2FKyleakin%3C%2FTD%3E%3CTD%3E(%2B0%3A50)%3C%2FTD%3E%3CTD%3E19%3A11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ELochalsh%2FKyleakin%3C%2FTD%3E%3CTD%3E(%2B0%3A50)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECanna%3C%2FTD%3E%3CTD%3E(%2B0%3A40)%3C%2FTD%3E%3CTD%3E19%3A01%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECanna%3C%2FTD%3E%3CTD%3E(%2B0%3A40)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMull%20of%20Kintyre%3C%2FTD%3E%3CTD%3E(-5%3A00)%3C%2FTD%3E%3CTD%3E13%3A21%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EMull%20of%20Kintyre%3C%2FTD%3E%3CTD%3E(-5%3A00)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPortree%3C%2FTD%3E%3CTD%3E(%2B0%3A55)%3C%2FTD%3E%3CTD%3E19%3A16%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPortree%3C%2FTD%3E%3CTD%3E(%2B0%3A55)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBunessan%3C%2FTD%3E%3CTD%3E(-0%3A15)%3C%2FTD%3E%3CTD%3E18%3A06%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EBunessan%3C%2FTD%3E%3CTD%3E(-0%3A15)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDOVER%3C%2FTD%3E%3CTD%3E(%2B5.30)%3C%2FTD%3E%3CTD%3E23%3A51%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EDOVER%3C%2FTD%3E%3CTD%3E(%2B5.30)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECLYDE%20(Inveraray)%3C%2FTD%3E%3CTD%3E(-5%3A30)%3C%2FTD%3E%3CTD%3E12%3A51%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECLYDE%20(Inveraray)%3C%2FTD%3E%3CTD%3E(-5%3A30)%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-450793%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-450976%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formula%20please...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320825%22%20target%3D%22_blank%22%3E%40pace36%3C%2FA%3E%20%2C%20what%20do%20you%20change%20exactly%2C%2018%3A21%3F%20What%20are%20other%20parameters%20for%20the%20formula%2C%20texts%20like%20(-0%3A47)%20in%20next%20row%20or%20that%20is%20result%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20if%20you%20attach%20small%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-451028%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formula%20please...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-451028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EThanks%20for%20replying.%3C%2FP%3E%3CP%3EColumns%20A-C%20are%20example.%3C%2FP%3E%3CP%3EI%20would%20input%20HW%20time%20of%20tide%20in%20cell%20H2%20(%26amp%3B%20height%20in%20G2)%3C%2FP%3E%3CP%3EFormula%20would%20then%20place%20HW%20Craobh%20in%20cell%20H7%20-%20Time%20is%20H2(18%3A21)%20less%20G7%20(-1-00)%20%3D%2017%3A21%3C%2FP%3E%3CP%3ESame%20formula%20for%20each%20port%20listed%20rows%208-20%3C%2FP%3E%3CP%3EH4%20is%202%3A17hrs%20before%20H2%20-%20Different%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-451035%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formula%20please...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-451035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320825%22%20target%3D%22_blank%22%3E%40pace36%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20convert%20your%20texts%20with%20time%20difference%20into%20the%20time%20(which%20is%20actually%20decimal%20number)%20and%20taking%20into%20account%20what%20time%20is%20always%20shall%20be%20positive.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3D%24H%242%2BIF(MID(G7%2C2%2C1)%3D%22-%22%2C-1%2C1)*MID(G7%2C3%2CLEN(G7)-3)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-451547%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formula%20please...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-451547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20a%20treat%20-%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-452627%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20formula%20please...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-452627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320825%22%20target%3D%22_blank%22%3E%40pace36%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
pace36
New Contributor

Can anyone help me with a time formula.

Left hand columns are example of what I'm trying to achieve.

HW Oban changes daily (manually input) all other locations change automatically through formula.

Thanks!

 

-- Friday 30/3/2018HW(BST) --  -- Friday 30/3/2018HW(BST) --
HW Oban 18:21  HW Oban 18:21
HW Crinan(-0:47)17:34  HW Crinan(-0:47)######
Dorus, C'veckan, Cuan turn at16:04  Dorus, C'veckan, Cuan turn at (-2:17:00)HW Oban######
        
        
Craobh(-1:00)17:21  Craobh(-1:00)######
Easdale(-0:25)17:56  Easdale(-0:25) 
West Loch Tarbert(-3:30)14:51  West Loch Tarbert(-3:30) 
Small Isles, Jura(-3:00)15:21  Small Isles, Jura(-3:00) 
Duart & Arisaig(+0:15)18:36  Duart & Arisaig(+0:15) 
Lochaline(+0:12)18:33  Lochaline(+0:12) 
Tobermory(+0:16)18:37  Tobermory(+0:16) 
Lochalsh/Kyleakin(+0:50)19:11  Lochalsh/Kyleakin(+0:50) 
Canna(+0:40)19:01  Canna(+0:40) 
Mull of Kintyre(-5:00)13:21  Mull of Kintyre(-5:00) 
Portree(+0:55)19:16  Portree(+0:55) 
Bunessan(-0:15)18:06  Bunessan(-0:15) 
DOVER(+5.30)23:51  DOVER(+5.30) 
CLYDE (Inveraray)(-5:30)12:51  CLYDE (Inveraray)(-5:30) 
5 Replies

@pace36 , what do you change exactly, 18:21? What are other parameters for the formula, texts like (-0:47) in next row or that is result?

 

Better if you attach small sample file.

@Sergei Baklan 

Hi Sergei

Thanks for replying.

Columns A-C are example.

I would input HW time of tide in cell H2 (& height in G2)

Formula would then place HW Craobh in cell H7 - Time is H2(18:21) less G7 (-1-00) = 17:21

Same formula for each port listed rows 8-20

H4 is 2:17hrs before H2 - Different formula

 

Hope that helps...

 

 

 

@pace36 ,

 

You need to convert your texts with time difference into the time (which is actually decimal number) and taking into account what time is always shall be positive.

 

That could be like

=$H$2+IF(MID(G7,2,1)="-",-1,1)*MID(G7,3,LEN(G7)-3)

@Sergei Baklan 

Works a treat - Thank you!

@pace36 , you are welcome