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)

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Patrick C. Simonds" wrote in message
...
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)

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.

--
Biff
Microsoft Excel MVP


"Patrick C. Simonds" wrote in message
...
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)