Custom Format of 00/:00 not calculating

[Shazz]

I have a spreadsheet which I am using 00:/00 format for the time so I don't have to manually input the : but I have a cell that needs to calculate between times and it is not working.

Can anyone advise.

Shazz
x

 

[Hui]

I've never heard of using a Custom Number format of 00:/00
and doubt that it will do what I think your trying to achieve

I don't believe it is possible to type 1030 as 10:30am without using helper columns or VBA which reformats the cell as it is entered

 

[NARAYANK991]

Hi ,

I think the format is :

00\:00

The value that you enter will need to be converted to a time value , by using a formula such as :

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

where A1 contains your entered value ; entering 745 will result in 07:45.

Narayan

 

[bosco_yip]

......by using a formula such as :
=INT(A1/100)/24 + MOD(A1,100)/24/60
where A1 contains your entered value ; entering 745 will result in 07:45.
Narayan

I think this formula will give the same result as the above formula:

=--TEXT(A1,"00\:00")

Regards
Bosco