converting a 7-digit julian date to a calendar date

I need to convert a 7-digiti julian date to a calendar date.  For example, my cell reads 2004029 but I need to convert it to read 01/29/2004.
Thanks.
0
anonymous (74718)
1/29/2004 10:41:10 PM
excel.misc 78881 articles. 5 followers. Follow

13 Replies
1376 Views

Similar Articles

[PageSpeed] 32

First of all that is not what is referred to as a Julian date
by astronomers nor   [as misused]  by  programmers.

  =DATE(left(A1,4), mid(A1,5,2), right(a1,2))

I have a page on Date and Time
     http://www.mvps.org/dmcritchie/excel/datetime.htm
as does Chip Pearson
     http://www.cpearson.com/excel/datetime.htm
I'm sure many others have pages on at least some aspect
of date and/or  time as well.

HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"j weber" <anonymous@discussions.microsoft.com> wrote in message news:01744A0E-1A56-4E2E-AFFA-43D81FFE92EE@microsoft.com...
> I need to convert a 7-digiti julian date to a calendar date.  For example, my cell reads 2004029 but I need to convert it to read
01/29/2004.
> Thanks.


0
dmcritchie (2586)
1/29/2004 11:04:31 PM
On Thu, 29 Jan 2004 14:41:10 -0800, "j weber"
<anonymous@discussions.microsoft.com> wrote:

>I need to convert a 7-digiti julian date to a calendar date.  For example, my cell reads 2004029 but I need to convert it to read 01/29/2004.
>Thanks.


  =DATE(INT(A1/10^3),1,MOD(A1,INT(A1/10^3)))


--ron
0
ronrosenfeld (3122)
1/30/2004 2:25:47 AM
Use:

=date(left(a1,4),1,right(a1,3))

One of the great things about the Date function is that it will cross over
month boundaries. So if you ask for the zeroth of a month, it will give you
the last day of the previous month. Similarly, in your case, it you ask for
the 56th of January, it will correctly figure out the 56th day of the year,
or 02/25/1994.

-- 
Regards,
Fred
Please reply to newsgroup, not e-mail


"j weber" <anonymous@discussions.microsoft.com> wrote in message
news:01744A0E-1A56-4E2E-AFFA-43D81FFE92EE@microsoft.com...
> I need to convert a 7-digiti julian date to a calendar date.  For example,
my cell reads 2004029 but I need to convert it to read 01/29/2004.
> Thanks.


0
fredsmith99 (155)
1/30/2004 3:37:14 AM
sorry misread it,  my eyes inserted another digit.  And yes that would
be a Julian date as known to programmers.   Also did not pick up
that you were trying to convert the date to show 1st of a month,
because I thought the 1 was already there,  but your other responders
were much more observant.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
  >
> "j weber" <anonymous@discussions.microsoft.com> wrote in message news:01744A0E-1A56-4E2E-AFFA-43D81FFE92EE@microsoft.com...
> > I need to convert a 7-digiti julian date to a calendar date.  For example, my cell reads 2004029 but I need to convert it to
read
> 01/29/2004.
> > Thanks.
>
>


0
dmcritchie (2586)
1/30/2004 4:21:18 PM
Similar question as above, except I need to convert 1040131 to 1/31/04. TIA

Greg
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:25gj10lloj345su9blo1urr5t1mugaatua@4ax.com...
> On Thu, 29 Jan 2004 14:41:10 -0800, "j weber"
> <anonymous@discussions.microsoft.com> wrote:
>
> >I need to convert a 7-digiti julian date to a calendar date.  For
example, my cell reads 2004029 but I need to convert it to read 01/29/2004.
> >Thanks.
>
>
>   =DATE(INT(A1/10^3),1,MOD(A1,INT(A1/10^3)))
>
>
> --ron


0
gregrivet (92)
1/30/2004 4:23:18 PM
On Fri, 30 Jan 2004 08:23:18 -0800, "Greg Rivet" <gregrivet@hotmail.com> wrote:

>Similar question as above, except I need to convert 1040131 to 1/31/04. TIA

You'll have to give more information.

"Above" is a Julian date characterized by digits that represent a year, and
then by the number of the day in that year.

I can't relate your number to that paradigm.


--ron
0
ronrosenfeld (3122)
1/30/2004 8:12:33 PM
Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
HTH.

Greg
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:eiel10tu108205bf82v74slre1023vj2o7@4ax.com...
> On Fri, 30 Jan 2004 08:23:18 -0800, "Greg Rivet" <gregrivet@hotmail.com>
wrote:
>
> >Similar question as above, except I need to convert 1040131 to 1/31/04.
TIA
>
> You'll have to give more information.
>
> "Above" is a Julian date characterized by digits that represent a year,
and
> then by the number of the day in that year.
>
> I can't relate your number to that paradigm.
>
>
> --ron


0
gregrivet (92)
1/30/2004 10:48:39 PM
Ron,

First, you are better off to create a new thread than add to an existing
one. Many people ignore threads with many replies because they assume the
problem has been address.

Second, what you have is a number which must be converted to a date. You do
that with three formulas, one for year, one for month, one for day.

The year is easy: =int(a1/10000)+1900
The day is easy: =mod(a1,100)
The month is more complicated because you have to use both -- first get rid
of the year, then get rid of the day: =int(mod(a1,10000))/100

You combine this with:

=date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))

-- 
Regards,
Fred
Please reply to newsgroup, not e-mail


"Greg Rivet" <gregrivet@hotmail.com> wrote in message
news:u7d00M45DHA.2712@tk2msftngp13.phx.gbl...
> Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
> HTH.
>
> Greg
> "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
> news:eiel10tu108205bf82v74slre1023vj2o7@4ax.com...
> > On Fri, 30 Jan 2004 08:23:18 -0800, "Greg Rivet" <gregrivet@hotmail.com>
> wrote:
> >
> > >Similar question as above, except I need to convert 1040131 to 1/31/04.
> TIA
> >
> > You'll have to give more information.
> >
> > "Above" is a Julian date characterized by digits that represent a year,
> and
> > then by the number of the day in that year.
> >
> > I can't relate your number to that paradigm.
> >
> >
> > --ron
>
>


0
fredsmith99 (155)
1/30/2004 11:06:59 PM
On Fri, 30 Jan 2004 17:06:59 -0600, "Fred Smith" <fredsmith99@yahoo.com> wrote:

>Ron,
>
>First, you are better off to create a new thread than add to an existing
>one. Many people ignore threads with many replies because they assume the
>problem has been address.

Perhaps you should address your complaint to the poster who asked the question,
rather than to the respondent.  Had I started a new thread with my response,
the party that asked the question would have had a good deal of difficulty
finding an answer.

>
>Second, what you have is a number which must be converted to a date. You do
>that with three formulas, one for year, one for month, one for day.
>
>The year is easy: =int(a1/10000)+1900
>The day is easy: =mod(a1,100)
>The month is more complicated because you have to use both -- first get rid
>of the year, then get rid of the day: =int(mod(a1,10000))/100
>
>You combine this with:
>
>=date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))

--ron
0
ronrosenfeld (3122)
1/31/2004 1:44:53 AM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:622m101hv8ppqreic1kjn88820bd92i9ub@4ax.com...
> On Fri, 30 Jan 2004 17:06:59 -0600, "Fred Smith" <fredsmith99@yahoo.com>
wrote:
>
> >Ron,
> >
> >First, you are better off to create a new thread than add to an existing
> >one. Many people ignore threads with many replies because they assume the
> >problem has been address.
>
> Perhaps you should address your complaint to the poster who asked the
question,
> rather than to the respondent.  Had I started a new thread with my
response,
> the party that asked the question would have had a good deal of difficulty
> finding an answer.


LOL!



-- 

Regards,

Peo Sjoblom


0
terre081 (3244)
1/31/2004 1:50:13 AM
On Fri, 30 Jan 2004 14:48:39 -0800, "Greg Rivet" <gregrivet@hotmail.com> wrote:

>Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
>HTH.


Ok, that makes it simple.  It's not really a Julian date.

I will guess that, for consistency 1/31/1999 would be represented as 990131.

That being the case, the conversion formula is:

 =DATE(INT(A1/10^4),MOD(INT(A1/100),100),MOD(A1,100))


--ron
0
ronrosenfeld (3122)
1/31/2004 1:55:16 AM
Sorry about that. "Ron" should have been "Greg".

-- 
Regards,
Fred
Please reply to newsgroup, not e-mail


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:622m101hv8ppqreic1kjn88820bd92i9ub@4ax.com...
> On Fri, 30 Jan 2004 17:06:59 -0600, "Fred Smith" <fredsmith99@yahoo.com>
wrote:
>
> >Ron,
> >
> >First, you are better off to create a new thread than add to an existing
> >one. Many people ignore threads with many replies because they assume the
> >problem has been address.
>
> Perhaps you should address your complaint to the poster who asked the
question,
> rather than to the respondent.  Had I started a new thread with my
response,
> the party that asked the question would have had a good deal of difficulty
> finding an answer.
>
> >
> >Second, what you have is a number which must be converted to a date. You
do
> >that with three formulas, one for year, one for month, one for day.
> >
> >The year is easy: =int(a1/10000)+1900
> >The day is easy: =mod(a1,100)
> >The month is more complicated because you have to use both -- first get
rid
> >of the year, then get rid of the day: =int(mod(a1,10000))/100
> >
> >You combine this with:
> >
> >=date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))
>
> --ron


0
fredsmith99 (155)
1/31/2004 11:36:33 AM
Thank you Fred

Greg
"Fred Smith" <fredsmith99@yahoo.com> wrote in message
news:efq9CX45DHA.3664@tk2msftngp13.phx.gbl...
> Ron,
>
> First, you are better off to create a new thread than add to an existing
> one. Many people ignore threads with many replies because they assume the
> problem has been address.
>
> Second, what you have is a number which must be converted to a date. You
do
> that with three formulas, one for year, one for month, one for day.
>
> The year is easy: =int(a1/10000)+1900
> The day is easy: =mod(a1,100)
> The month is more complicated because you have to use both -- first get
rid
> of the year, then get rid of the day: =int(mod(a1,10000))/100
>
> You combine this with:
>
> =date(int(a1/10000)+1900,int(mod(a1,10000))/100,mod(a1,100))
>
> --
> Regards,
> Fred
> Please reply to newsgroup, not e-mail
>
>
> "Greg Rivet" <gregrivet@hotmail.com> wrote in message
> news:u7d00M45DHA.2712@tk2msftngp13.phx.gbl...
> > Ron the 1040131 is a date format in access the 104=2004, 01=Jan, 31=day.
> > HTH.
> >
> > Greg
> > "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
> > news:eiel10tu108205bf82v74slre1023vj2o7@4ax.com...
> > > On Fri, 30 Jan 2004 08:23:18 -0800, "Greg Rivet"
<gregrivet@hotmail.com>
> > wrote:
> > >
> > > >Similar question as above, except I need to convert 1040131 to
1/31/04.
> > TIA
> > >
> > > You'll have to give more information.
> > >
> > > "Above" is a Julian date characterized by digits that represent a
year,
> > and
> > > then by the number of the day in that year.
> > >
> > > I can't relate your number to that paradigm.
> > >
> > >
> > > --ron
> >
> >
>
>


0
gregrivet (92)
2/2/2004 5:50:47 PM
Reply:

Similar Artilces:

VBA using VC++ 7.1
I am trying to convert an application from VC++ 6.0 to VC++ 7.1 that has support for VBA. We are using VBA 6.3. I am getting errors trying to build this applcaion in VC++ 7.1 using unmanaged code. I am getting the following error... I have tried building the VBA sample called multipad and it also does not compile with the following error. ERROR: ----- c:\Program Files\VBA6SDK\Include\objext.h(331) : error C2011: 'IProfferService' : 'struct' type redefinition c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\PlatformSDK\Include\ShObjIdl.h(4124) : see declaratio...

Format cells #7
I have Excel 2000, Win ME. Recently the pulldown command to format cells does not always respond - ie, no box appears, so I can't apply borders or control number format. The shortcut approach doesn't work either. I've tried Help..Detect and Repair but the fault remains. Any suggestions welcome. ...

Odd date problem
I'm reposting this as this is a very strange problem in Access. I'm using 2003. After my entry is an entry by missinglinq via AccessMonster.com who was able to reproduce the error. Looking forward to comments/fixes. Thanks, Rob I have the following in the DblClick event for the control CaseStart which is a date/time field. The problem is that once the control is double clicked the form remains in edit mode, even though I've told it not be editable. Additionally, when the control is double clicked, it shoudn't enter in the time until the Edit button has been clicked o...

You a 7-Day FREE Trial #2
We Are Offering You a 7-Day FREE Trial to the internet's Hottest New Business Opportunity! If you missed out on the DOT COM boom, now is your chance to cash in on the massive and growing global demand in our $20 BILLION PER YEAR market. We'll show you how to create an income that will come to you for years and grow with each passing month. A SIMPLE, Fully-Automated, REJECTION-FREE Internet Marketing System! Finally, an EASY, proven, FREE system that will truly help anyone willing to follow our guidance succeed! Average People Are Making Money! Average people are putting their home...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

how to convert excel's .cvf file to .csv file
...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

me w/CERTIFICATE -Digital ID still blocked.
me add signed/approved CERTIFICATE and it show. me at Windows Mail/Tools/Internet Accounts/(*select account)/Poperties/Security/Signing Certificate/Select/(*Select Certificate)/Encrypting Preferences/Select/???/Select Default Account Digital ID/???(only "Cancel" depresion is available [because no CERTIFICATE DISPLAY]). the Algorithm/3DES. me problem lies at the ??? of the formula. me can-not cee now to aplies ze CERTIFICATE to Email Account Setting. ya\ *=item(s) r available. ty 4 yur replies. $$$ $$$ -- UNIVERSAL RULER; OWNER FEDERAL GOVERNMET; GOD OF UNITED STATES;...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Outbox displays Received Date
Need some help on this one. I'm using Outlook 2000 on a Windows XP machine and for some odd reason the Outbox now displays the "Received" date and the Inbox displays the "Sent" date. How do i correct this. It seems to be hidden in the PST file because when I login on a different machine it shows the same thing. Right click on the column headers and choose the "Field Chooser". Drag the field you want to see from the Field Chooser into the column headers area. Drag column headers you don't want to see away from the column headers. "Lee" ...

Invoice Due Date #2
Hello, I'm using Money Small Business 2006 and need some help. How can I specify the due date on a invoice? It seems to automatically use the date that the invoice is created on. All help appreciated. Thank you. > How can I specify the due date on a invoice? It seems to automatically > use the date that the invoice is created on. Below the Line Item table there is a link "To add or edit billing and shipping addresses, notes, or other information, go to the Invoice Details". Click that link and you can specify the Payment Terms. I don't see where to specify a cet...

Unread Messages #7
Every year, my outlook goes nuts and all my folders in my favorites folders disappear and are replaced with archived files. I have to reorganize it. I have done so again but I used to have a file for unread messages but I can't find it. I thought it might be in the search folders in the "all mail folders" but it's not there. This is a really handy folder that I use often. Can someone tell me how to relocate it? Thanks. File-> New-> Search Folder -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ ...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

eConnect 7.5
We are using eConnect 7.5 to recieve Sales Orders from our customers and also send back 'Order Acknowledgements' and Invoices Back to the customer. We want to send back one SOP message when we print the delivery note and one message when we create the Invoice. However each time we print the sales transaction another message is created. We only want to resend the message if the data in the transaction changes not each time we print the transaction. Can we configure eConnect to work as we wish? Regards, David ...

Change cell colour by date
Hi All I'm working on a spreadsheet for a maintenance program. I'm looking for a way to change the cell colour depending on when a task is due. For example: when a monthly task is due I would like the cell colour to change to remind me that it is due in a week. Thanks Use Conditional formatting, see details in Help! Regards, Stefi „Dri” ezt írta: > Hi All > I'm working on a spreadsheet for a maintenance program. I'm looking for a > way to change the cell colour depending on when a task is due. For example: > when a monthly task is due I would like ...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

Problem converting from Quicken to M2005
My Quicken files are mostly investment related, and generally converted fine. However all bonds (regular and muni's) converted as Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) How do you change the Investment Type for an item? Thank you. In microsoft.public.money, Mike wrote: >My Quicken files are mostly investment related, and generally >converted fine. However all bonds (regular and muni's) converted as >Investment type: Mutual Fund, not Bond. (1) How do I prevent that, (2) Money typically converts custom data types from Quicken into funds. I thou...

Windows 7 explorer crashes frequently (No 7 community?)
First, I looked for a Windows 7 community, which is where this question belongs. Is there one that I missed? Anyway Many 7 users and I are having explorer crashes while we are browsing the file system. For me it usually happens when using an auto run context menu with my DVD. If you click on the message it will start right back up with no problem, but it's getting to be a nuisance. I have disabled a Mozilla addon, Better Privacy, which some said was suspect, but no change. I also looked for a registry value that was said to be causing it, but it's non existant i...

I need reminder box for tasks&flags-but NO box for calendar remind
I know how to turn on/off the reminder sound & notification box under options-advanced options. Is there a way to have the reminder notification box activate for tasks & flags, but have NO reminder bos for calendar appointment reminders? No, you'd need to not set reminders on the calendar appointments. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlo...

Numbers converting to decimal
I a trying to figure out why when I type 11 and automatically converts it to .11, if I type 11. it will stay 11,if I change all the cells to text then back to number they willstay. I have checked the formatting of the cells, it even happens when I open a brand new worksheet. Any ideas? Thanks Dawn Hi Dawn, Tools>Option>Edit, uncheck Fixed Decimal -- Kind Regards, Niek Otten Microsoft MVP - Excel "DawnP" <anonymous@discussions.microsoft.com> wrote in message news:c3cf01c48a05$d75359d0$a501280a@phx.gbl... > I a trying to figure out why when I type 11 and &...

Importing Entourage Date
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop The board on my old MacBook has just died but I managed to clone the disc and drag all the data out and onto an external drive. All my data is there along with what seems to be the complete system however I can't import the Entourage files, database and folders into my iMac! I've tried to locate the folders and files using import then searching through the Documents / Office 2008 identities etc. but when I get to the database folders they are 'greyed' out and I can't attach th...