If you need to pad cell in Excel to a certain length you need to use function REPT().
For example if you need to left-pad value “113” to the length of 12 characters,
you use this formula:
=REPT(" ", 12 - LEN(C5)) & C5
If you need to convert dates from Gregorian (MM/DD/YYYY) format to Julian (YYYDDD)
you can use this formula:
=(YEAR(C5) - 1900) * 1000 + C5 - DATE(YEAR(C5),1,1) +1
To convert from Julian back to Gregorian in Excel, use this formula:
=DATE(C5/1000+1900,1,1) + MOD(C5, 1000) -1
A little tutorial:
Today is 2012-12-22 SAT. In Julian this looks like this – 112357. Last date in 2012 looks like this – 112366. And Julian 113001 convert to a very festive Gregorian 2013-01-01.
In all examples above, the source value is located in cell “C5″
