Can anyone help me to convert the format of time entered from HHMM to
HH:MM in the same cell.

For example:
Date entry converts to, in the same cell
0840 08:40
1150 11:60

Thanks
QD

=(INT(A1/100)+MOD(A1,100)/60)/24

and format as time

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"QD" wrote in message
oups.com...
Can anyone help me to convert the format of time entered from HHMM to
HH:MM in the same cell.

For example:
Date entry converts to, in the same cell
0840 08:40
1150 11:60

Thanks
QD

Why would 1150 become 11:60? (or is it just a typo?)

I don't think you can do that directly in the same cell unless it's for
display purposes only, in which case you could use a custom format of

00\:00

If you actually want to change the value then you need to do that with VBA
or a formula in another cell, e.g.

=TEXT(A1,"00\:00")+0



"QD" wrote:

Can anyone help me to convert the format of time entered from HHMM to
HH:MM in the same cell.

For example:
Date entry converts to, in the same cell
0840 08:40
1150 11:60

Thanks
QD