I want to write a cell formula that counts ‘base’ 12, that is there are two inputs and one output - Example1 - input 1 = 10, input 2 = 2, then the output result is 4, i.e. counting round the clock from ten to two is 4 (hours). Example 2 - input 1 = 7, input 2 = 1, then result is 6. Example3 - input 1 = 3, input 2 = 2, then output result is 11. It is the boundary line of the counting crossing the 12 (o’clock) position. I’m having trouble writing formula that works succinctly while also doing the simple counts such as input 1 = 5, input 2 = 9, output = 4.
I only use the logical reference R1C1 structure in my formulae.
What you need is most likely the “remainder of the difference” between the times given, in a division by 12. With the MOD() function this also works with “negative time” (crossing the twelve `o’clock mark).
-
=MOD(<end time> - <start time> ; 12)
If you are working with “proper” spreadsheet time values, you may need to multiply times by 24 first, or use 0.5 for the divisor, depending on how your spreadsheet is organized. This is due to the nature of time data in spreadsheets: Time unit is “day” which amounts to 24 hours, so 12 hours is half a day, or 0.5
1 Like
HI
Kjell
Thanks so much - that formula/function works perfectly,
Regards
Andy