Julian Date conversion

Kerry Michael

New Member
Joined
Apr 21, 2013
Messages
7
Hi Everybody!

I m trying to calculate interest and discounts.

I have a Julian date of (lets say today) (13111)E12345 in a loan receipt number for accounts in my A/R.

How do I convert the Julian date into a normal date? I only want to use the 13111 and exclude any integers after that.

Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi Everybody!

I m trying to calculate interest and discounts.

I have a Julian date of (lets say today) (13111)E12345 in a loan receipt number for accounts in my A/R.

How do I convert the Julian date into a normal date? I only want to use the 13111 and exclude any integers after that.
There are at least three different interpretations of what Julian Dates mean... please tell us what date (and time?) your example number describes (what I am interested in is what starting date your Julian Dates count from... actual Jan 1 no matter what day of week that is? some other starting count date which you will need to describe)?
 
Upvote 0
Hi Rick. Thank you for replying. The receipt number of 13111 for us means 13 is year 2013, and 111 means today. This is the Julian date the invoices are received, and will always be the starting date in the calculations. Our discounts and penalties are based on this Julian date. If the invoice was paid within 30 days of the Julian date a 1% discount is applied, if paid 31-60 days from Julian date no discount is taken. For every month after the 60 day Julian date a 1% penalty is applied. So if the invoice expected amount is 7000.00 and was paid on 09-13-13, the payment should be 7000.00 X 3 months. Kerry
 
Upvote 0
Hi Rick. Thank you for replying. The receipt number of 13111 for us means 13 is year 2013, and 111 means today. This is the Julian date the invoices are received, and will always be the starting date in the calculations. Our discounts and penalties are based on this Julian date. If the invoice was paid within 30 days of the Julian date a 1% discount is applied, if paid 31-60 days from Julian date no discount is taken. For every month after the 60 day Julian date a 1% penalty is applied. So if the invoice expected amount is 7000.00 and was paid on 09-13-13, the payment should be 7000.00 X 3 months. Kerry
You did not actually answer my question, but I think you are saying the Julian Date counts days from January 1st no matter what day of the week that falls on. Fine. But what was the E12345 part of the cell value for... is that part of the date (the time perhaps?) and, if so, is it to be factored into the output you are looking for? Assuming not, this formula may be what you want (I have assumed your Julian Dates are in Column A because you didn't tell us where they actually are)...

=DATE(2000+LEFT(A2,2),1,RIGHT(A2,3))
 
Upvote 0
In column A I have the receipt number of 13111E12345. The 13111 is a Julian Date. The E12345 is all other receipts for that day, and is not factored.
 
Upvote 0
Hi.

Or this:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' >13110</td><td align='right' >04/20/2013</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' >13111</td><td align='right' >04/21/2013</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' >13112</td><td align='right' >04/22/2013</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>=DATE</Span><Span style='color:#0000DD'>(2000+INT</Span><Span style='color:#222222'>(A1/1000)</Span><Span style='color:#0000DD'>,1,MOD</Span><Span style='color:#222222'>(A1,1000)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr></table>
 
Upvote 0
Hi.

Or this:

AB
11311004/20/2013
21311104/21/2013
31311204/22/2013

<COLGROUP><COL><COL width=60><COL width=60></COLGROUP><TBODY>
</TBODY>

CellFormula
B1=DATE(2000+INT(A1/1000),1,MOD(A1,1000))

<COLGROUP><COL><COL></COLGROUP><TBODY>
</TBODY>
A1 does not have 13110 in it... it has 13110E12345 in it (where the 12345 is not a fixed value).
 
Upvote 0
In column A I have the receipt number of 13111E12345. The 13111 is a Julian Date. The E12345 is all other receipts for that day, and is not factored.
Okay, so did the formula I posted in Message #4 produce the correct dates for you?
 
Upvote 0
Okay, so did the formula I posted in Message #4 produce the correct dates for you?
Yes!! The formula works but only if I use just the Julian date. So how do I exclude any integers after the Julian date. So if A1 is 13211E12345 I just want 13211 considered to create an output of 07/30/2013 in A2.
 
Upvote 0
Yes!! The formula works but only if I use just the Julian date. So how do I exclude any integers after the Julian date. So if A1 is 13211E12345 I just want 13211 considered to create an output of 07/30/2013 in A2.
He-he, I grabbed the wrong formula to post... this is the one I meant to post...

=DATE(2000+LEFT(A2,2),1,MID(A2,3,3))
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top