Making cost code custom format

[csteps]

the cell needs to be "03-15-23" and I make a custom format ##-##-## but it leaves out the 0 at the beginning. what custom format can i make for this

 

[Joe4]

Try this instead:
0#-##-##

 

[csteps]

Try this instead:
0#-##-##

Thanks for the response, I forgot to mention that sometimes it will need to be something like 12-15-20 or 22--18-07

 

[Joe4]

I forgot to mention that sometimes it will need to be something like 12-15-20 or 22--18-07

Yes, that is a very critical detail that you left out that changes the question quite a bit.
How will it now when to use a single dash and when to use multiple dashes, like in 22--18-07?
You haven't told us the logic for making that determination.

What exactly do your unformatted values look like?
Do they already have dashes in them?
If so, then they are Text entries, and Custom Formatting can only be applied to Numeric entries.
To do anything with that would probably require VBA.

 

[csteps]

Yes, that is a very critical detail that you left out that changes the question quite a bit.
How will it now when to use a single dash and when to use multiple dashes, like in 22--18-07?
You haven't told us the logic for making that determination.

What exactly do your unformatted values look like?
Do they already have dashes in them?
If so, then they are Text entries, and Custom Formatting can only be applied to Numeric entries.
To do anything with that would probably require VBA.

That second dash is a mistake all will be ##-##-## with that first # sometimes being a 0 and sometimes being 1=9

 

[Joe4]

That second dash is a mistake all will be ##-##-## with that first # sometimes being a 0 and sometimes being 1=9

Did you try what I recommended in the first post?
If your values are truly entered as number (so that Custom Formatting will work on them), then that should work in both instances.
Don't be fooled by the 0 at the beginning. If you have a two digit number first that does not start with a zero, it will NOT change it to a zero.
Try it and see.

 

[csteps]

Did you try what I recommended in the first post?
If your values are truly entered as number (so that Custom Formatting will work on them), then that should work in both instances.
Don't be fooled by the 0 at the beginning. If you have a two digit number first that does not start with a zero, it will NOT change it to a zero.
Try it and see.

It did work but then I tried 11-12-18 and it makes it 04-34-16 for some reason

 

[Joe4]

It did work but then I tried 11-12-18 and it makes it 04-34-16 for some reason

If you are manually entering it, then you want to enter it WITHOUT the dashes, and let Excel add the dashes.
So you would just enter 111218.

If you try entering 11-12-18, Excel is seeing it as a date entry, and doing a conversion on it.
Excel stores dates as the number of days since 1/0/1900, so 11-12-18 gets converted to 43416 (to see this, enter the date 11-12-18 in any blank cell and change the cell format to General).
So then it is just applying the 0#-##-## format to the 43416 number.

 

[csteps]

If you are manually entering it, then you want to enter it WITHOUT the dashes, and let Excel add the dashes.
So you would just enter 111218.

If you try entering 11-12-18, Excel is seeing it as a date entry, and doing a conversion on it.
Excel stores dates as the number of days since 1/0/1900, so 11-12-18 gets converted to 43416 (to see this, enter the date 11-12-18 in any blank cell and change the cell format to General).
So then it is just applying the 0#-##-## format to the 43416 number.

Thank you so much! that was exactly my problem

 

[Joe4]

You are welcome.
Glad we were able to figure it out!