Cell ID help

J

JVS

Question: I am looking for a way to flag a user when they enter a "text"
value in a cell.
I have a formula in cell F11, this is close but I had to hard code the cell
"ID" as part of the error message and it does not update automatically when
the formula is copied to the next row
Formula from F11:=IF(COUNTA(C11)=1,"Delete C11",IF(COUNTA(D11)=1,"Delete
D11",IF(COUNTA(E11)=1,"Delete E11",IF(D11+E11>0,(D11-C11)+E11,"0:00"))))

"Is there an easy to capture the cell ID if that cell contains a text value?
I tried using data validation rules for "time" but it limits the range from
0:00 to 23:59 and I have a lot of associates that work later than that work
till 2:00 to 4:00 am and they are use to enter their time as 8:00 to 26:00
or 28:00 which properly calculates their time."

My problem is that the users enter "space bar" or 8;00 vs. a valid time
value, this results in a #value error message and call for help.
Any suggestions are greatly appreciated.

Thanks!
Johnny
 
P

Peo Sjoblom

Use data>validation, allow time and use 00:00 as starts and 23:59 as end
time and tell them to use
14:00 for 2 PM and 02:00 for 2 AM
 
J

JVS

Using this method, when I clock in at 8:00(am) then clock out at 02:00 it
results in -6:00 hours worked vs. 18:00 hours.
Any other suggestion?

Is there a function that will match a value and return the cell ID (C3) or
will Excel only
return the value in a cell?

Thanks for suggestion!
Johnny
 
P

Peo Sjoblom

That is because you are doing it the wrong way..

=MOD(End-Start,1)

or

=MOD(B1-A1,1)

Or

=(A1>B1)+B1-A1

where B1 holds 02:00 and A1 08:00
 
J

JVS

Cool!
Thanks for your help/time!

Peo Sjoblom said:
That is because you are doing it the wrong way..

=MOD(End-Start,1)

or

=MOD(B1-A1,1)

Or

=(A1>B1)+B1-A1

where B1 holds 02:00 and A1 08:00

--

Regards,

Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top