Keeping leading 0s when combining fields

[discskyer]

Hello,

I am having a problem keeping the leading 0s in my fields when combining multiple fields. Basically, I am formatting the date to look like yyyyddmm, but it always cuts out leading 0s for the day and month. So instead of getting 20040305, I am getting 200435. Can anyone help with that? Thanks!

 

[discskyer]

Oops, forgot to mention that I'm using Excel 2000.

Currently, I have broken up the cells by using the year(), day(), and month() functions and formatting the cells to show two numbers for day and month. Thanks!

 

[Beowulf005]

Try changing the format of the cell containing Year(),Day().... to text cells rather then date, number or general cells. (Excel auto removes beginning 0's when the cell is formated at date, general, or number.) Changing it to text will not remove the 0 and you can still us it for the concat function

GL

When frustrated remember, in the computer world there is almost always a backdoor.

 

[SkipVought]

Hi,

How are you formatting your date? Please be specific.

Format/Cell/Number Tab -- CUSTOM yyyymmdd will work UNLESS you do not have real dates.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[KenWright]

If you really have to have your Year/Month/Day in separate fields, then it doesn't matter what you format them as, combining them via a normal concatenation will result in the loss of the leading zeroes.

This can be overcome by the use of the TEXT() function, eg

=A1&TEXT(B1,"00")&TEXT(C1,"00")

or you might want to actually try adding the day to the month to the year so that you end up with a real date and then just format that as Skip suggested.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------