Forcing 2 digits for month in Date

  • Thread starter Thread starter onliner
  • Start date Start date
O

onliner

I have downloaded data which contains a column of review dates. The dates
are formatted as mm/dd/yyyy.
I have code that expects the date to always be 8 digits (and apparently
text) in the form of, "05152004".
The current code treats the month segment as
=If(Left(A1,2)="05", Then do something...
which currently fails for months 1 thru 9.
I know I could do; =If(Month(A1)=5, etc. but this would involve finding and
replacing every instance of date-handling code. I was hoping that I could
perform a one-time vba procedure on the date column such that the stored
month segment was two digits.
I've tried various date format changes attempting to get month numbers less
than 10 to be treated as 2-digits by the code, but I've had no luck.
Is there a way to "force" the leading zero for the month - not just what is
displayed on screen but what is actually stored in the cell?
Thanks,
Bud
 
I don't quite understand. If your data is, indeed, text and is 8 digits the
formula you cite should work.
01012004
=If(Left(A1,2)="01",dothis,dothat) would dothis
 
Don,
To clarify...
The dates in the column are currently "true" dates in the format,
"mm/dd/yyyy".
I'm trying to convert and store them in the cells as text in the format,
"mmddyyyy" with the month segment always being two digits, "05152003" and
never "5152003"

Bud
 
Since you are having this problem re a leading 0, the column would seem to contain true Excel
dates rather than text.

Dates are not stored with a year, month, and date segment. They are stored as the number of days
that have elapsed since some arbitrary "day 0". Excel uses Dec 31, 1899 as "day 0". If a cell
contains the date Aug 8, 2003, the actual value in the cell is the number 37,841. So the idea of
forcing Excel to "store a leading zero for the month" doesn't apply.

If your formulas (not "code" -- code usually means VBA stuff) expect the cells to contain text,
you could convert them to text: insert a new column B, use the formula =TEXT(A1,"MMDDYYYY"), and
copy down. Then convert these formulas to their values via Edit/Copy, then Edit/Paste
Special/Values, pasting over the original dates. Then delete column B.

The problem with this approach is that you can no longer do date arithmetic on these text
entries. You can't subtract them to get elapsed time, MONTH, DAY, and YEAR won't work, etc.
OTOH, if your formulas always use text functions rather than date functions, that isn't
relevant.

Personally, I suggest you "bite the bullet" and redo the formulas the "right" way, using date
functions like MONTH (for this particular example). If you do that, the sheet should recalculate
faster and could well be smaller, too.
 
Thanks Myrna.
For this project, the formula you provided; =TEXT(A1,"MMDDYYYY") is what
I've been looking for.
Long term,you're right...I'll have to "bite the bullet" and change the
current code to handle "true" dates.
Thanks, again.
Bud
 
Back
Top