Time formatting

[insafeuste]

Hey guys,

Quite a newbie here so please bear with me. The issue is rather simple but it seems that despite the amount of searching I have done I'm unable to find a simple fix. I am creating an employee time sheet that takes in clock in/clock out time in two separate cells, then subtracts the two and returns a number of hours worked. This is all fine as long as AM and PM are specified in each cell, but when a user omits this rule or does not write the time in military format, I am left with a negative value (Ex. 9:00 and 5:00 would produce -4). I've attempted to write a macro that adds 12 to any negative value in my "hours worked" column but have been unsuccessful. I have very little programming experience and don't remember VB too well. Is there a simple solution I am missing? And if not, could someone please point me in the right direction on how to create this script? Thank you in advance.

[davegugg]

Does it have to be in VBA? You could use the following formula, assuming your clock in value is in G13 and your clock out value is in H13.

=IF(SIGN(H13-G13)=-1,0.5+H13-G13,H13-G13)

[insafeuste]

Amazing, a much more simple solution and presented to me within minutes of my original post. Thank you I really appreciate it. If you find the time, could you break down the above function just for the sake of my curiosity/understanding?

[davegugg]

The first part of the if checks if the difference is negative. If so, add .5 (which is 12 hours) to the first number, than calculate the difference. If not, just calculate the different.

Excel stores time and dates as decimals and integers. Today, Nov 2nd at midnight is 40849. Tomorrow will be 40850. Today at noon was 40849.5. Make sense?

[insafeuste]

I was unfamiliar with the sign function but yes that does makes sense. Very helpful, thank you.

[ConneXionLost]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save