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
    Post Iniad
    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.