Home Forums Tech Office and Applications Pad cells and convert from Gregorian to Julian in Excel

Viewing 1 post (of 1 total)
• Author
Posts
• #2340
Participant

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”

Viewing 1 post (of 1 total)
• You must be logged in to reply to this topic.