Trying to concatenate two dates with a "To" between to show a beginning and
Ending date. Unfortunately, Excel keeps posting the dates as a serial number.
Since this is to be presented to a customer that will not work. I need the
dates in 06/13/2006 to 06/12/2007 format. I am using the formula below. I
have tried to change the number format using the Format Cells - Number -
Date and General and Text settings, none of which work... can anyone assist?

=IF('Service Renewal raw'!AA4="","",IF('Service Renewal
raw'!AB4="","",CONCATENATE('Service Renewal raw'!AA4, " to ",'Service Renewal
raw'!AB4)))

Thanks,
Don

gotta format em
format(yourdate,"yourformat")
format('Service Renewal raw'!AB4,"mm/dd/yyyy")

--
Don Guillett
SalesAid Software

"Don Ellet" <Don wrote in message
...
Trying to concatenate two dates with a "To" between to show a beginning
and
Ending date. Unfortunately, Excel keeps posting the dates as a serial
number.
Since this is to be presented to a customer that will not work. I need the
dates in 06/13/2006 to 06/12/2007 format. I am using the formula below. I
have tried to change the number format using the Format Cells - Number -
Date and General and Text settings, none of which work... can anyone
assist?

=IF('Service Renewal raw'!AA4="","",IF('Service Renewal
raw'!AB4="","",CONCATENATE('Service Renewal raw'!AA4, " to ",'Service
Renewal
raw'!AB4)))

Thanks,
Don

"Don Ellet" <Don wrote in message
...
Trying to concatenate two dates with a "To" between to show a beginning
and
Ending date. Unfortunately, Excel keeps posting the dates as a serial
number.
Since this is to be presented to a customer that will not work. I need the
dates in 06/13/2006 to 06/12/2007 format. I am using the formula below. I
have tried to change the number format using the Format Cells - Number -
Date and General and Text settings, none of which work... can anyone
assist?

=IF('Service Renewal raw'!AA4="","",IF('Service Renewal
raw'!AB4="","",CONCATENATE('Service Renewal raw'!AA4, " to ",'Service
Renewal
raw'!AB4)))

By using CONCATENATE you are making the cell text, so you need to format the
dates as text accordingly:
Try
=IF('Service renewal raw'!AA4="","",IF('Service renewal
raw'!AB4="","",CONCATENATE(TEXT('Service renewal raw'!AA4,"mm/dd/yyyy")," to
",TEXT('Service renewal raw'!AB4,"mm/dd/yyyy"))))
--
David Biddulph

On Tue, 13 Jun 2006 09:18:01 -0700, Don Ellet <Don
wrote:

Trying to concatenate two dates with a "To" between to show a beginning and
Ending date. Unfortunately, Excel keeps posting the dates as a serial number.
Since this is to be presented to a customer that will not work. I need the
dates in 06/13/2006 to 06/12/2007 format. I am using the formula below. I
have tried to change the number format using the Format Cells - Number -
Date and General and Text settings, none of which work... can anyone assist?

=IF('Service Renewal raw'!AA4="","",IF('Service Renewal
raw'!AB4="","",CONCATENATE('Service Renewal raw'!AA4, " to ",'Service Renewal
raw'!AB4)))

Thanks,
Don

You need to use the TEXT function when you concatenate dates:

CONCATENATE(TEXT('Service Renewal raw'!AA4,"mm/dd/yyyy"), " to ",TEXT('Service
Renewal raw'!AB4,"mm/dd/yyyy")


--ron

Thanks for all your answers...this is the one I used.

"David Biddulph" wrote:

"Don Ellet" <Don wrote in message
...
Trying to concatenate two dates with a "To" between to show a beginning
and
Ending date. Unfortunately, Excel keeps posting the dates as a serial
number.
Since this is to be presented to a customer that will not work. I need the
dates in 06/13/2006 to 06/12/2007 format. I am using the formula below. I
have tried to change the number format using the Format Cells - Number -
Date and General and Text settings, none of which work... can anyone
assist?

=IF('Service Renewal raw'!AA4="","",IF('Service Renewal
raw'!AB4="","",CONCATENATE('Service Renewal raw'!AA4, " to ",'Service
Renewal
raw'!AB4)))

By using CONCATENATE you are making the cell text, so you need to format the
dates as text accordingly:
Try
=IF('Service renewal raw'!AA4="","",IF('Service renewal
raw'!AB4="","",CONCATENATE(TEXT('Service renewal raw'!AA4,"mm/dd/yyyy")," to
",TEXT('Service renewal raw'!AB4,"mm/dd/yyyy"))))
--
David Biddulph