I am attempting to create a custom format and I am not having much luck. I have a suspense tracker sheet that I am developing and each task will have a number. I have found documentation that I should be able to simple enter the number of the task into the block and a special format will make it look how I want. What I want to see in my cell at the end of the day is



ADMIN 2014-0001

I have tried creating a custom number format, but Excel keeps giving me an error message that my format is invalid. I am typing

"ADMIN" yyyy-###0

Yeah, I don't think I've ever seen date format characters ('y') combined with general numerals ('#' and '0'). I'm not surprised it won't work. You'll have to do this another way.

Ok, let's break this down into pieces. First, if you want ADMIN 2014-0001, then you don't want "###0" in the format string; that would get you ADMIN 2014-1 ... even if you got the other parts to work, I mean. So for that last part you're going to want '0000', not '###0'.

Second, how to combine this with the year? The value that's being formatted isn't a date, is it? I mean, if you want 2014-01-15 to be displayed as 2014-0001 and 2013-12-31 as 2013-0365, then we're talking about displaying a date in quasi-Julian format; that's one problem. But if you just have a bunch of numbers from 0 to 9999 and you want them all to appear as ADMIN 2014-nnnn then it can still be done, it's just a different problem.

One way would be to just make the custom format string "ADMIN 2014-"0000. That's great—simple and effective—but come next year you'll have change it. Is that all you need, or would you prefer something more complicated but flexible?

I'm going to take the simple and effective solution as I am feeling very lazy about this project and want it done sooner rather than later.


Thank you for solution.