Date format??

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

Hi! I have an Excel file that I have imported into
Access. My dates are broken up into four different
fields and I need to combine those fields. I need my
dates to be in the YYYYMMDD format. My only problem is
that my Year, Month, and Day fields show only one digit
when there is a 1-9 used. Example

Century Year Month Day
20 1 5 16
19 98 12 8
19 84 9 20
20 3 10 15

In Excel, I tried to change the format of the number
using a custom format, and it changed the look of the
number, just not that value in the cell.

Ex: You could see 05 when you looked at the cell, but
when you looked in the cell at the value it said 5

Then, I tried to use the CONCANTENATE function to combine
all of the date fields together into one field thinking
that the format issue may have corrected my problem, but
it didn't. So I imported the file into Access hoping
there was some update function (maybe the update query)
that I could use.

Are there any suggestions on how I could get the Year,
Month, and Day to show a 2 digit field in either Excel or
Access? I need that leading zero to show not only when
you look at the field, but also when you look in the
value in the cell. Thanks in advance for any help
given! Have a great day!

Julie
 
Hi! I have an Excel file that I have imported into
Access. My dates are broken up into four different
fields and I need to combine those fields. I need my
dates to be in the YYYYMMDD format. My only problem is
that my Year, Month, and Day fields show only one digit
when there is a 1-9 used. Example

Century Year Month Day
20 1 5 16
19 98 12 8
19 84 9 20
20 3 10 15

In Excel, I tried to change the format of the number
using a custom format, and it changed the look of the
number, just not that value in the cell.

Ex: You could see 05 when you looked at the cell, but
when you looked in the cell at the value it said 5

Then, I tried to use the CONCANTENATE function to combine
all of the date fields together into one field thinking
that the format issue may have corrected my problem, but
it didn't. So I imported the file into Access hoping
there was some update function (maybe the update query)
that I could use.

Are there any suggestions on how I could get the Year,
Month, and Day to show a 2 digit field in either Excel or
Access? I need that leading zero to show not only when
you look at the field, but also when you look in the
value in the cell. Thanks in advance for any help
given! Have a great day!

Julie
Click to expand...

If I understand you correctly, you would like to have the date displayed in a
single cell in the yyyymmdd format.

If that is the case, use this formula:

=DATE(Century*100+Year,Month,Day)

and format the result as yyyymmdd


--ron
 
Julie said:
Hi! I have an Excel file that I have imported into
Access. My dates are broken up into four different
fields and I need to combine those fields. I need my
dates to be in the YYYYMMDD format. My only problem is
that my Year, Month, and Day fields show only one digit
when there is a 1-9 used. Example

Century Year Month Day
20 1 5 16
19 98 12 8
19 84 9 20
20 3 10 15

In Excel, I tried to change the format of the number
using a custom format, and it changed the look of the
number, just not that value in the cell.

Ex: You could see 05 when you looked at the cell, but
when you looked in the cell at the value it said 5

Then, I tried to use the CONCANTENATE function to combine
all of the date fields together into one field thinking
that the format issue may have corrected my problem, but
it didn't. So I imported the file into Access hoping
there was some update function (maybe the update query)
that I could use.

Are there any suggestions on how I could get the Year,
Month, and Day to show a 2 digit field in either Excel or
Access? I need that leading zero to show not only when
you look at the field, but also when you look in the
value in the cell. Thanks in advance for any help
given! Have a great day!

Julie
Click to expand...

You realise, I hope, that YYYYMMDD is not a valid date format with which
Excel will be able to calculate? The best you will get is a text string or
number, but not a date (in Excel's terms). To achieve this, you could use
(for data in cells A1:D1):
=A1&IF(B1<10,"0","")&B1&IF(C1<10,"0","")&C1&IF(D1<10,"0","")&D1
to obtain a text string, or
=VALUE(A1&IF(B1<10,"0","")&B1&IF(C1<10,"0","")&C1&IF(D1<10,"0","")&D1)
to obtain a number.
 
Julie

Assuming that the century is in column A try

=DATE(A3*100+B3,C3,D3)

Copy the formula down then select them all and copy them
and then paste special as values. You should not have to
format the dates but if you get serial numbers format the
range as dates.

Regards
Peter
 
Back
Top