I have a time cell in my excel sheet having a custom format hh:mm. While applying it in any formula it treats as numeric value which excel keeps for time cell.
Is there any way to retrieve it as text ?
The purpose is if I entered 10:78 in this time cell it automatically converts to 0.470833333 instead of triggering validation or converting into 11:18. I can set custom format only since i am generation this excel using NPOI
So if i am able to read 78 ican include the condition in the data validation formula.
I tried MINUTE() but it returns 18 instead of 78
Am really stuck with this. Any help should give me a great relief.
Format your cell as Text, so user input doesn't get converted.
Use custom validation with formula =AND(VALUE(LEFT(A1;2))<24;MID(A1;3;1)=":";VALUE(RIGHT(A1;2))<60) (example for cell A1)
However, if I were you I would prefer to disable data validation and allow Excel to correct inputted data.