So I have an excel sheet where I am trying to make it so we don't have to type the ":", meaning I can just type 0200 and have it show 02:00. Which I can do with custom formatting, issue is I also have a formula that is used to calculate the overall time between 2 entries. If I change it so that "0130" converts to "01:30", the time calculation no longer works correctly.
The formula currently for calculating time is " =IF(B287=0,"",(B287+E287)*24-(A287+D287)*24) "
But when I enter the information using "00\:00" that formula no longer calculates correctly. Not sure if there is an easy way to do what I what I am trying to do, but essentially whether I type " 0130" or "01:30" I want it to display "01:30".
Thanks for any feedback.
The formula currently for calculating time is " =IF(B287=0,"",(B287+E287)*24-(A287+D287)*24) "
But when I enter the information using "00\:00" that formula no longer calculates correctly. Not sure if there is an easy way to do what I what I am trying to do, but essentially whether I type " 0130" or "01:30" I want it to display "01:30".
Thanks for any feedback.