Hi,
I'm having trouble calculating hours worked in decimal format between two Time in/time out values that are in text format. Values such as 09:00 for time in and 17:00 for time out. When trying to create a formula to calculate the difference, I'm getting errors due it being text format and have been unsuccessful converting it to number. I would also like to convert the hours worked to decimal so that 15 minutes would be 0.25.
See the attached spreadsheet for an example import. 00:00 shifts are midnight.
When reviewing the data, I encountered several incorrect calculations, such as the ones attached. Do you know why it's calculating like this and how to fix it?
Also, it's not in decimal format. For the first row for EE 16734, it should be 0.43 for 26 minutes instead of 12:26.
In Excel, I would calculate the shift time in decimal by converting the military time to AM/PM and then using a formula such as =(Shift end time - shift start time) * 24. EZM is giving me a "#Can't subtract not numbers" error when I try to use a similar formula.
This might be close to what you are looking for. It is a bit of a brute force method. But you should be able to clearly see the steps. Please see the daily hours column at the end.
I assumed your time out at 00 hours is midnight so cast this to 24. I also assumed everyone clocks out the same day they clock in. So, I just broke out the hour and minute component of the time in and time out using keep before and keep after. Then convert the hours to minutes and add the minutes. Subtract the in total minutes from the out total minutes. Divide the difference by 60 and truncate everything after the decimal. Use the remainder function with 60 to get the number of minutes left over that don't easily divide and then divide that by 60 to get the fraction you are looking for.
Is this close to what you are looking for? I am not sure the exact format of your desired output.
Please keep in mind, that the numeric value is a day and the decimals are the hours, minutes and second of a day. In your calculation you assume that the decimals are a frac tion of an hour instead of a day.
