- This topic has 0 replies, 1 voice, and was last updated 11 years, 9 months ago by .
Viewing 1 post (of 1 total)
Viewing 1 post (of 1 total)
- You must be logged in to reply to this topic.
Home › Forums › Tech › Office and Applications › Pad cells and convert from Gregorian to Julian in Excel
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”
© 2017 DomainWebCenter.com. All Rights Reserved. | Disclaimer | Contact the Editor