time sheet help, format TIME and formula based on the actual time entered.

I have a few questions and hope anyone can help me with it. If you think I can be helped with macros please let me know and I’ll move my question over there.
I’m working with employee time cards, which I downloaded in excel from our program. From some reason, the values in column A and B cannot be changed to TIME format (in the ‘downloaded info’ sheet)*. The reason I need it to be time, is because I need another formula to show that if an employee clocked in after 9pm the time worked should return as 0. For ex, D2 should be 0.
Or, if the format can’t be changed, I could continue to work with the number the program give me (column C) and then add a separate formula that if the value in A2 is after 9pm, the hours worked should be 0 (it doesn’t necessarily have to change in column C, I could have it somewhere else, because In reality I’m working with 2 workbooks, one with the original info downloaded, and the second one which references all the times and I have the formulas there to calculate the pay.)
Not sure any of these are possible, but appreciate any feedback.

*I did a test (see sheet ‘test’) which I manually entered times, and then did Replace pm with space pm and that worked to change to TIME format (see sheet ‘test2’) but for the downloaded info (in the ‘downloaded info’ sheet) it’s not working.

Hi
Use this formula to convert your download time into real time (Convert column A and column B)

Formula:

Please Login or Register  to view this content.

thank you for your responses! Jose, yours worked perfect, thanks! John, yours works as well (and it does save a step) however the outcome of your formula is time, and the actual hours worked I need as a general number. Can you change that? Also, any input that after 9pm the hours worked should be 0?

actual hours worked I need as a general number

2:17 (hh:mm) as a number 2.17 is nonsensical, especially if you SUM it.

2:45 + 4:30 = 7:15 not 6.75 as a number

Figures from total of Days 1 to 3

Number hh:mm
10.80 11:20
10.87 11:27
10.37 10:37

For after 9 pm start:

=IF(A2="","",IF(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))>21/24,0,MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1)))

2:17 (hh:mm) as a number 2.17 is nonsensical, especially if you SUM it.I'm trying to round it, not sum it.
=if(c2>=2,2,0)
will look at the other formula now

Originally Posted by bh mng

2:17 (hh:mm) as a number 2.17 is nonsensical, especially if you SUM it.

I'm trying to round it, not sum it.
=if(c2>=2,2,0)
will look at the other formula now

Originally Posted by bh mng

For after 9 pm start:

this worked perfect, you're amazing! but same issue as before, if I get a time value I can't round that number. Our guidelines for pay are a bit different as conventional workplaces that's why I need these type of equations.
When I'll have time I'll study exactly what you did there, as it is worth knowing it...

So what is the rule regarding rounding?

This will round down i.e ignore the minutes: format cells as General or Number

=IF(A2="","",IF(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))>21/24,0,INT(MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1)*24)))

Worked thank you so much.

You are very welcome and thank you for the feedback and rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.