Cell ID help

[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

 

[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

 

[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

 

[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

 

[JVS]

Cool!
Thanks for your help/time!

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