can you format a cell with a formula?

[smiler44]

I have this formula and would like to format the cell to 00:00 at the same time.

The formula is = if (sheet2!E3 = "",right(B6,4)
I want to have the cell display in the format 00:00 but am getting 0000

Thank you
smiler44

 

[Brianwarnock]

http://www.techonthenet.com/excel/formulas/format_string.php

Does this help

Brian

 

[NBVC]

is the result you are looking for TIME?

If so, try:

=IF(Sheet2!E3="",REPLACE(RIGHT(B6,4),3,0,":")+0,"")

Then format as time to show as you desire.

if it is not a TIME and you want a text result, remove the +0 part.

 

[smiler44]

Brian, thank you for the link I had a read but it went over my head.
NBVC, your spot on. I created a new sheet to test.
This was the source information 0730-1255
This the formula =IF(E1="",REPLACE(LEFT(D1,4),3,0,":"))
and this the result 07:30

thank you very much NBVC, really appreciate it.


smiler44