MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time VBA problem

Posted by Gerald Marshall on December 01, 2001 9:33 AM

I am a complete novice at VBA procedures, however I have copied a procedure that allows me to enter time in a cell as e.g 1630 and it should automatically convert to 4:30PM.
When I enter 1630 in the specified cell it changes to 12:00PM,and not 4:30PM however when I move away from the selected cell and then return to it, the time then changes to 4:30PM which is correct.

What am I doing wrong?
Any help appreciated.

Gerald Marshall

Posted by luke on December 01, 2001 11:38 AM

I think it starts with excel trying to turn your time into a date and leaving the timeslot empty 0:0:00 which is 12:00 pm
you can try inserting the code as timevalue, but this needs a string where hours and minutes are separated bij a colon timevalue("16:30")
also you can try timeserial which also needs hour minute and seconds separate but separated by a comma and can be inserted as variables timeserial(hour,minute,seconds) or timeserial(16,30,00)
hope it helps a bit