Return a 3 digit number

[Patrick C. Simonds]

The line below returns the day of the year (Julian date) so will return a
number from 1 to 365. Is there any way to get this to always return a 3
digit number? If the number is 1 then I need 001 if it is 45 I need 045 and
if it is 241 then I need 241.

=C4-DATE(YEAR(C4),1,0)

 

[Ashish Mathur]

Hi,

Try this

=TEXT(C4-DATE(YEAR(C4),1,0),"000")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

You could use a custom number format of 000 however, any leading zeros will
be for *display* purposes only. For example, 045 would be displayed but the
true underlying value of the cell will be 45.

You can use a formula like this:

=TEXT(C4-DATE(YEAR(C4),1,0),"000")

That will return a *TEXT* string in the form 045.