Is there a way to add a leading zero to a date that is 7 digits and should be 8?
7301982 should be 07301982.
I have a column full of these values, and need a way to do so with a formula. Any ideas?
Add a comment |
9 Answers
I know this is an oldie, but when I googled for a solution this was the first result.
What I did was:
=concatenate(year(A1),text(month(A1),"00"),text(day(A1),"00")) Where A1 is a date field.
1 Comment
andrej
This is similar solution as I made today: I used the ampersand "&" instead of "concatenate", it looks like
TEXT(YEAR(C3), "0000")&TEXT(MONTH(C3),"00")&TEXT(DAY(C3),"00").=text(A1, "00000000") will do it.
2 Comments
Dustin Laine
This is a solution for maintaining the leading zeros in a formula.
Patrick Conwell
This would not fix 721983 into 07021983. This would only work if the day is already 2 digits long.
Set a custom format of 00000000
3 Comments
guitarthrower
only problem with this answer is that if it is January 1st 1982 you will get 00111982. The format should be mmddyyyy
stuartd
Guitarthrower, because Excel stores dates as numbers that's not going to work in this scenario. If I put the value 111982 in a cell and apply the custom format mmddyyyy I get 05/08/2206, if I put in 7301982 and apply that format Excel considers the date to be out of range..
guitarthrower
hmmm... good point. how to solve then? it seems a formula might be needed. because what you've listed will only work for days 10-31 of the first 9 months of the year. Unless I'm understanding incorrectly.
Just another thought since this just happened on my new laptop. It could be your windows settings. If you prefer leading zeroes on the month everywhere in windows (like the lower right hand clock) then you can:
Control Panel >> Clock, etc >> Change Date, Time or Number Formats... then set the Short Date to MM/dd/yyyy.
This also carries over to Excel as the first date format. I know it is not a formula exactly as asked, but this is the article I found while searching.
Comments
This is a good formula when you need leading zeros so another application sees a 9 digit number.
- Add a column to your spreadsheet (Column B if your data is in Column A)
- Use this formula in the new column:
=REPT(0,9-LEN(A2))&A2&"" - Get the 1st cell, then drag down as much as you need.
- Remember to copy/paste
option 123to save as data. Otherwise, you'll see data but in reality it is a formula and you will receive reference errors if you try to use the data incolumn B.
9 digits and column B are variables. You can use any length or any column on your spreadsheet. Just adjust the formula.
Comments
Copied from another answer on a different site, worked for my like a charm!
ok. It seems that your dates are formatted as text. This is what you should do.
first, on a blank cell somewhere on the sheet, type the number 1. then, right click, copy. next, highlight the entire column of dates. right click, paste special, multiply. all of the dates will have turned into numbers. next, highlight the date column, and apply the date format that you want.
Comments
There is a simple way to maintain the leading zeroes in Excel.
Simply add this to the cell and type whatever value you need and the zeroes will be retained
For ex: If I want 0000000023
Type into a cell '0000000023
That ' symbol seems to retain the zeroes as long as you type it before the values.
