simple question regarding formatting in a cell with a formula

[scottnshelly]

hey guys,
i have a formula that looks similar to: =CONCATENATE(A1,B1,C1,D1,E1,F1
which as you know adds the contents of these cells into one cell.
cells c1 and e1 are numbers in time format (i.e. 17:00). when i put i
the formula it reads those numbers in text format or something and the
come out as decimals. my question is: how do i get the formatting t
time. i've tried changing the formatting by going to format > cells
but no soup.
thanks

 

[David Benson]

Scott & Shelly,

As you probably know, Excel stores times (and also dates) in number formats.
Dates are whole numbers with "1" representing January 1, 1900 (usually),
while times are decimal fractions between "0" and "1".

To get your data into a string in time format, you will have to use the TEXT
function.

=CONCATENATE(A1,B1,TEXT(C1,"h:mm"),D1,TEXT(E1,"h:MM"),F1)

-- David

 

[scottnshelly]

Thanks,
sounds good, but when i put the formula in it gave me 0:00:00.
i hate to sound as ignorant as i am, but what did i do wrong

 

[scottnshelly]

this worked for a while, but now when i do it, i have to go and eras
the concatenate formula and re-type it in. then it will sta
concatenated, and i will have to erase it and re-type it again. wh
isn't this refreshing, so to speak?
maybe you have a different and better idea.
i have a workbook with a userform with 4 textboxes and a combobox.
need the text from the four textboxes to be combined with other tex
and put into an e-mail body. the easiest way is to just put one cel
as the body. that is why i have been concatenating.
what should i do differently?
thank