I am trying to write a formula field that calculates the difference between 2 time values. The time inputs are picklist values that are in the format h:mm aa some valid values are
- 12:00 AM
- 9:30 AM
- 6:15 PM
How can I get the number of hours as a decimal between 2 inputs. For example the difference between 9:30 AM and 6:15 PM is 8.75
Best Answer
Use the LEFT, RIGHT and FIND functions to grab the hours and minutes. Use the VALUE function to convert the strings to numbers. Multiply hours by 60 and add to minutes.
So 9.30 becomes 540 + 30 = 570
If PM convert to 24 hour equivalent (I.e add 12 to hours) 6.15 = 18 x 60 + 15 = 1095
Then subtract to get the difference and divide by 60
1095-570= 525 / 60 = 8.75