Date using 8a as 8:00 AM to find time differences. IF Function formula not working.

Please Help. I have a time in and a time out, and then a formula total. I have to use 7p for in and 8a for out. Can't get it to work.

Put this in C7:

=--SUBSTITUTE(SUBSTITUTE(B7,"a",":00:00"),"p",":00:00")-SUBSTITUTE(SUBSTITUTE(A7,"a",":00:00"),"p",":00:00")+IF(RIGHT(B7)="p",0.5)-IF(RIGHT(A7)="p",0.5)

then copy down.

If you have any entries where the IN time is PM and the OUT time is AM (i.e. working through midnight into the next day) then you will need another adjustment to this.

Hope this helps.

Pete

It is possible that I could have an employee work through midnight. Could you tell me what the adjustment would be?

Also what about half hour like 7.5?

You will need to add the following to the end of the formula that I gave you earlier:

+AND(RIGHT(A7)="p",RIGHT(B7)="a")

then copy the formula down, as before.

Even this is not foolproof if someone started at, say, 11 AM then worked through to 2 AM the next morning. but hopefully that will be a very rare occurence.

Hope this helps.

Pete

Originally Posted by adburris

Also what about half hour like 7.5?

Moving to fractional hours is significantly different than your posted example, and would involve a substantial re-working of the formula.

Pete

What if it is like 530p? for the fractional hours.

Thank you for all of your help by the way. I worked on this for hours with no avail.

Why can't you enter the data as proper times - it would then be much easier to manipulate.

Even moving to a system like 530p would mean major changes to the formula I gave you, and it would also mean that you would have to enter all times in that format, i.e. 100p instead of 1p.

Pete

The data is being dumped into the spreadsheet in that format. The hope is to find a formula that calculates it without retyping the information.

Post another example which shows the full range of variations that you might encounter.

Pete

See Attached

The formulae are more complicated so in the attached file I've used two helper columns to convert your time representations into times that Excel recognises, and then just subtracted them to get the total hours.

Hope this helps.

Pete

Thank you. You are a genius.

Thanks for the kind words.

If this fixes your problem, perhaps you can mark the thread as Solved (the FAQ describes how to). Also, you can pass on your thanks more directly by clicking on the "star" icon in the bottom left corner of any post that you have found to be helpful.

Pete