Help with calculating time

Originally Posted by 6StringJazzer

Welcome to the Forum marios80!

It is possible to do this, but that's a very undesirable design. Using two times requires a simple subtraction, but using a string that like requires a complicated formula to break down the string and parse each piece. Also it is more likely that the user will make an unnoticed data entry error, and writing the formula to detect such errors is even more complicated. Using two separate cells for start and end times is the standard solution to this--why do you need it in one cell?

The person I did this for is real picky. Its my GIRLFRIEND LOL!!! I'm trying to clean it up for her and display it the way she wants it. I understand that the data entry will need to be real specific otherwise it would cause an error but if you could help me with the formula I would really appreciate your help.

Hi - try this:

Cell A1 : 8:00 am - 5:00 pm
Cell B1 : =(TIMEVALUE(MID(A1,(FIND("-",A1))+1,((LEN(A1))-(FIND("-",A1))))))-(TIMEVALUE(LEFT(A1,(FIND("-",A1))-1)))

You'll need to make sure there is a gap between "8:00" and "am" and similarly between "5:00" and "pm" or the formula won't work.

This also takes care of your second question ("put an "X" in the cell it will calculate the hours worked as "0" (zero).")

Cheers
AW

Have a look at this

http://www.excelforum.com/excel-form...a-problem.html

Originally Posted by amit.wilson

Hi - try this:

Cell A1 : 8:00 am - 5:00 pm
Cell B1 : =(TIMEVALUE(MID(A1,(FIND("-",A1))+1,((LEN(A1))-(FIND("-",A1))))))-(TIMEVALUE(LEFT(A1,(FIND("-",A1))-1)))

You'll need to make sure there is a gap between "8:00" and "am" and similarly between "5:00" and "pm" or the formula won't work.

This also takes care of your second question ("put an "X" in the cell it will calculate the hours worked as "0" (zero).")

Cheers
AW

Thank you for the formula! It works fine except for the "X" part. When I put "X" in the cell it gives me "#value!"

Anyway you can possibly fix that?

Originally Posted by Toonies

Have a look at this

http://www.excelforum.com/excel-form...a-problem.html

That's also a good template but not quite what I was looking for but thanks for the help.

Sorry - here is the correct formula:

Cell A1 : 8:00 am - 5:00 pm
Cell B1 : =IF(A1="x",0,(TIMEVALUE(MID(A1,(FIND("-",A1))+1,((LEN(A1))-(FIND("-",A1))))))-(TIMEVALUE(LEFT(A1,(FIND("-",A1))-1))))

You could also try this alternative:

Cell A1 : 8:00 am - 5:00 pm
Cell B1 : =IFERROR((TIMEVALUE(MID(A1,(FIND("-",A1))+1,((LEN(A1))-(FIND("-",A1))))))-(TIMEVALUE(LEFT(A1,(FIND("-",A1))-1))),0)

With the first alternative, you'll get '#Value' if you type in anything other than the time of X. With the 2nd alternative, you won;t get '#Value!'.

Cheers

Ok, I actually figured it out with the template that "Toonies" referred to. Here's the correct template that I needed. Thanks to everyone for their help!

wow! I saved the wrong document. Oh well. I have to update it again tomorrow and then re upload the correct one.

Here is the corrected template if anyone is interested. Thanks!