Date format

  • Thread starter Thread starter silly_jem
  • Start date Start date
S

silly_jem

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 said:
With 1997 in A1, use:
=DATE(A1,1,1)
--
Gary''s Student - gsnu200859


silly_jem said:
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?
Click to expand...
Click to expand...
 
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.
 
Format the cell the way you like.

Format|cells|Number tab
(xl2003 menus)

silly_jem said:
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 said:
With 1997 in A1, use:
=DATE(A1,1,1)
--
Gary''s Student - gsnu200859


silly_jem said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top