One of the tricky challenges in using excel is dealing with different text formats. I’ve discussed dates in the past but hours can also prove to be very tricky. Why? Basically, excel can understand all of these:
What is cannot do though is guess which format you (or the end user) is using. I was asked to create a wage calculator that would be able to manage all formats. There are no easy ways to do this simply because excel cannot guess what the user is actually thinking.. Of course, I could look for “AM” or “PM” and do something when I find those, but it does remain difficult to deal with. Much easier is to make it very clear to the end user how you’d like him to enter the data.
Let’s take a simple wage calculator where the user has to enter the hours that he worked in order to for me to know how much I owe him. You can see a screenshot here:
In this case, I made it very clear that I expected him to enter the hour in one cell and the minutes in another. That makes it much easier for me to manage the data. I then can simply use a nested if condition to determine the actual time:
Why? First of all, I deter,ine the time by adding the hours to minutes/60. I also want to add 12 hours if it is “pm”.
I did pretty much the same thing to determine the end hour:
Then, I can simply substract one from another right? Almost.. but what if the user worked from 6pm (or 18:00) to 3am? Then you would get a negative number. Instead, I simply look and add 24 hours if that is the case:
After that, it becomes fairly straight forward, as you can see:
You can also download the spreadsheet