I would like to convert a number to a the first day of a year, i.e., 1997 to
01/01/1997. How do I do this? I know how to create text that LOOKS like
this [=TEXT(a1,"01\/01\/0000")], but it seems like this is a text format and
not a date format, so I can't use this "date" to subtract from another date
(to calculate the time interval between two dates).

Can you help me?

With 1997 in A1, use:
=DATE(A1,1,1)
--
Gary''s Student - gsnu200859


"silly_jem" wrote:

I would like to convert a number to a the first day of a year, i.e., 1997 to
01/01/1997. How do I do this? I know how to create text that LOOKS like
this [=TEXT(a1,"01\/01\/0000")], but it seems like this is a text format and
not a date format, so I can't use this "date" to subtract from another date
(to calculate the time interval between two dates).

Can you help me?

Thank you. That worked, but it converted to date to a serial number. Can I
get it back into a date format (mm/dd/yyyy)?

"Gary''s Student" wrote:

With 1997 in A1, use:
=DATE(A1,1,1)
--
Gary''s Student - gsnu200859


"silly_jem" wrote:

I would like to convert a number to a the first day of a year, i.e., 1997 to
01/01/1997. How do I do this? I know how to create text that LOOKS like
this [=TEXT(a1,"01\/01\/0000")], but it seems like this is a text format and
not a date format, so I can't use this "date" to subtract from another date
(to calculate the time interval between two dates).

Can you help me?

To convert from TEXT to a date, use the double unary minus
=--TEXT(A1,"01\/01\/0000") or use =DATEVALUE(TEXT(A1,"01\/01\/0000"))
In either case, format the cell as date.
--
David Biddulph

"silly_jem" wrote in message
...
I would like to convert a number to a the first day of a year, i.e., 1997
to
01/01/1997. How do I do this? I know how to create text that LOOKS like
this [=TEXT(a1,"01\/01\/0000")], but it seems like this is a text format
and
not a date format, so I can't use this "date" to subtract from another
date
(to calculate the time interval between two dates).

Can you help me?

Format the cell the way you like.

Format|cells|Number tab
(xl2003 menus)

silly_jem wrote:

Thank you. That worked, but it converted to date to a serial number. Can I
get it back into a date format (mm/dd/yyyy)?

"Gary''s Student" wrote:

With 1997 in A1, use:
=DATE(A1,1,1)
--
Gary''s Student - gsnu200859


"silly_jem" wrote:

I would like to convert a number to a the first day of a year, i.e., 1997 to
01/01/1997. How do I do this? I know how to create text that LOOKS like
this [=TEXT(a1,"01\/01\/0000")], but it seems like this is a text format and
not a date format, so I can't use this "date" to subtract from another date
(to calculate the time interval between two dates).

Can you help me?

--

Dave Peterson

is there any formula if we copy that and change month,
Example,
1/1/1997
1/2/1997
1/3/1997

Zafar

Regards

"David Biddulph" wrote:

To convert from TEXT to a date, use the double unary minus
=--TEXT(A1,"01\/01\/0000") or use =DATEVALUE(TEXT(A1,"01\/01\/0000"))
In either case, format the cell as date.
--
David Biddulph

"silly_jem" wrote in message
...
I would like to convert a number to a the first day of a year, i.e., 1997
to
01/01/1997. How do I do this? I know how to create text that LOOKS like
this [=TEXT(a1,"01\/01\/0000")], but it seems like this is a text format
and
not a date format, so I can't use this "date" to subtract from another
date
(to calculate the time interval between two dates).

Can you help me?