Pad cells and convert from Gregorian to Julian in Excel

[Post Iniad]

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”

[Author]

Posts