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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies