# Julian Date conversion

#### Kerry Michael

##### New Member
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Rick Rothstein

##### MrExcel MVP
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)?

#### Kerry Michael

##### New Member
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

#### Rick Rothstein

##### MrExcel MVP
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))

#### Kerry Michael

##### New Member
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.

#### Matt Rogers

##### Well-known Member
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>

#### Rick Rothstein

##### MrExcel MVP
Hi.

Or this:

 A B 1 13110 04/20/2013 2 13111 04/21/2013 3 13112 04/22/2013

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

 Cell Formula 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).

#### Rick Rothstein

##### MrExcel MVP
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?

#### Kerry Michael

##### New Member
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.

#### Rick Rothstein

##### MrExcel MVP
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))

Replies
3
Views
372
Replies
3
Views
535
Replies
5
Views
472
Replies
4
Views
403
Replies
3
Views
278

1,195,909
Messages
6,012,256
Members
441,687
Latest member
urimagic

### 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.

### Which adblocker are you using?

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

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