Date format
 

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?

Date format
 

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?

Date format
 

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?

Date format
 

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?

Date format
 

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

Date format
 

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?