Convert date

How can I convert a date in this format (mm-dd-yyyy) to a 
Julian-date (i.e., YYYYDDD)?

For example:  09-13-2003  to  2003256


0
anonymous (74722)
9/8/2004 5:24:57 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
475 Views

Similar Articles

[PageSpeed] 4

Hi
see:
http://www.cpearson.com/excel/jdates.htm

-- 
Regards
Frank Kabel
Frankfurt, Germany


gary wrote:
> How can I convert a date in this format (mm-dd-yyyy) to a
> Julian-date (i.e., YYYYDDD)?
> 
> For example:  09-13-2003  to  2003256
0
frank.kabel (11126)
9/8/2004 5:28:58 PM
On Wed, 8 Sep 2004 10:24:57 -0700, "gary" <anonymous@discussions.microsoft.com>
wrote:

>How can I convert a date in this format (mm-dd-yyyy) to a 
>Julian-date (i.e., YYYYDDD)?
>
>For example:  09-13-2003  to  2003256
>

Here's one way, with the date in A1:

=TEXT(A1,"yyyy")*1000+A1-DATE(YEAR(A1),1,0)

Format the cell as General or as Number with 0 decimal places.


--ron
0
ronrosenfeld (3122)
9/8/2004 7:03:12 PM
Reply:

Similar Artilces:

Convert Alpha to Numeric
I have two spreadsheet which I would like to compare. One has numeric data, the other has the same data but the references codes have been entered as alpha. Does anyone know how this can be converted to numeric? Many thanks for your help. Not sure, but maybe as a start, you could try something along these lines using VLOOKUP and CODE() ? Assume the sample (numeric) data below is in Sheet1 cols A and B, with the numeric refcodes in col A RefCode Field1 65 Text1 66 Text2 67 Text3 etc And you have in Sheet2, the alphas as the RefCodes in col A RefCode Field1 A Text1 B Text2 C Text3 etc ...

Query error after converting
I'm running Access 2003. I have a working database. It seems as if the database is in Access 2000 format, because when I go to Convert, the 'Convert to Access 2000' option is grayed out. I need to convert this database to both Access 2002-2003 and Access 97. I have this part of VB code, and it works fine in the current version, as well as in Access 2002-2003 (after converting to Access 2002-2003). Dim rstRoutes As Recordset Dim strSQL As String ...... strSQL = "SELECT DISTINCT [Route] " & _ "FROM Highways_Info " & _ "WHERE ...

Date on Template
I created some templates and am choosing to bring over a date to be shown in the template when it is run. I have selected this attribute to be date only and not date & time. Every time I run the template it still shows date+time+time zone. Is there a way to show only the date and not the rest of the data. Thanks for any help anyone can provide. ...

[more of a 'c' question really] Converting doubles to strings without getting scientific notation
Hello hello.. I'm using _gcvt in a dialog to display a double, but when it gets small the string appears in scientific notation ie 0.02 become 2e-02 Is there another conversion routine I can use to prevent this? I have a not terribly scientific customer who would prefer a straight forward decimal display. Thanks "Zardoz" <thing@thing.thing> wrote in message news:122lvvkvicks85nka58iugok64ba0plpem@4ax.com... > Hello hello.. > > > I'm using _gcvt in a dialog to display a double, but when it gets > small the string appears in scientific notation ...

converting from text to numerical number
I have a spread sheet of entire year schedule. The format as following: 1 2 3 .... 31 row 1 row 2 row 3 Jan row 4 row 5 Feb row x Dec I would like to replace column 1 up to Jan to 1. Replace the cell below Jan to Feb to 2. etc. Would anyone help. Thanks in advance. You could select cells A1:A3, type a 1, then press Ctrl+Enter Select A4:A5, type a 2, and press Ctrl+Enter And so on, till all twelve months are done. james wrote: > I have a spread sheet of entire year schedule. The format as following: > > 1 2 3 .... 31 &...

Calculate Dates with If Then Else Statements
I need to calculate total years’ full time experience (i.e. 2.5 years, 3.25 years, etc.) in an Access 2003 database form. This is what I am trying to do: If the Full-Time End Date is Blank, use Today's Date, ELSE if the Full-Time End Date has a Date, use that Date. Separately, these two formulas work: =DateDiff("m",[FullTimeStartDate],Now())/12 =DateDiff("m",[FullTimeStartDate],[FullTimeEndDate])/12 My problem is writing a statement to calculate using the END DATE if one is entered, or NOW, if there is no end date. I have tried many combinations, but can’t ge...

Filter Date
Hi, I have a form which contains a listbox (holding the values Process Date and Quantity) and I have a textbox which displayed the current month. What I want to do is when I click on a command button (cmdMonthDown) and the textbox value changes from May to April I want the values in the listbox to be filtered so that only those values are disiplayed that were entered in April. If I'm not making sense please let me know otherwise your help would be appreciated. Thanks Reference the text box as a parameter in the list box's RowSourcee, e.g. if the text box shows the month name in fu...

Task start/end dates from MS project to Workspace site
We are currently running MSPS 2007 with WSS 3.0. I am trying to create a list in one of the project work spaces. I would like to auto-populate two columns in that list with the start and end date of certain tasks from the Project Plan in MS project. I am thinking we can do this by linking these columns to the the project SQL database fields. So, 1) Do I use the published SQL databse for this? 2) Which fields in the SQL database would I have to link to get the Start and End dates? EggHeadCafe - Software Developer Portal of Choice AutoList in ASP.Net http://www.eggheadcafe.com/tutorial...

Date Format when incoming date can be 0
I have a date fiels yyyymmdd which can have a value of 0 or a valid date in recent past. Excel displays the 0 date as 01/01/1900 and the non zero dates as expected. Cant figure this out , any help would be appreciated. Excel uses a sequencial number from either 1/1/1900, or 1/1/1904 (depending on your options) as the date value for Example 6/3/2005 is 38506 using the 1/1/1900 if the cell is formatted as a date it is responding with the date it thinks you mean. If you want the cell to display a zero =if(Date=0,"0",date) "DP NY10601" wrote: > I have a date fiels y...

how to calculate the substraction between two dates
hi, i have two cells ,in which there are two dates. (for instance ,one cel l 08/02 ,the other 07/30) if the gap of the two dates is 2 days , something will be set to do. but it is difficult to judge the the substraction between two dates any good ideas. thanks Perhaps this might be of help .. Assume the *dates* "2-Aug-2004" and "30-Jul-2004" are input in A1 and B1, and A1:B1 is custom formatted as: mm/dd viz. it'll show as: In A1: 08/02 In B1: 07/30 Try in C1: =DAY(ABS(A1-B1))-1 Format C1 as: General or Number C1 will return: 2 i.e. the number of days...

Converting a number to Hour & Min format
I am attempting to take a basic number, say 2000, and convert the number into an hour and minute format (i.e. 33:33) only. Everytime I use the custom format tab or the time/date tab I get a date included or an AM/PM setting. Any ideas or suggestions? Thanks in advance. Hi, For any number to be converted into time format you need to divide tha number by 24, since Excel time code is 24 (hr) code. then you forma the divided answer (83.33) into time, you will get 8:00 regards sazi -- Message posted from http://www.ExcelForum.com Use Format/Cells/Number/Custom [hh]:mm In article <...

Date Range
Hi, When I want the date rage appeared on the report header, I use textbox and put (qry criteria) in its recordsource. It works fine. However, For this time, I got a message like this ‘First([Between [From this date] And [To this date]])" Is there something wrong with the qry criteria? Would you please show me how to fix the problem? Thanks Chi It's very hard to understand the crtieria, can you post the full SQL What is the First in the beginning? Why there are to square brackes in the end And in the beinning? First([Between [From this date] And [To this date]])" T...

What is a good program to convert pdf files to word?
I want to be able tosave pdf files as word files. PDF is essentially a graphics format. How well it can be converted to Word format rather depends on what it contains. The most reliable method is to use OCR software, such as Finereader. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<>...

how to convert msg files into wav file.
hey, i have recorded some voice on a pocket pc i have. i transfered that t the pc. now these are *.msg files and wanted to convert the files int wav files. is there a way i can do this.! i have been trying for quit sometime and have been unsuccessful. please reply.! Rites ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

download the Credit Card transaction date not the the posting date
Then I download a statement from a Credit card and import it in to MS Money I always get the Posting Date not the Transaction date. Do I need another column in the register, help does not tell me how to do that? Or is the only solution changing each entry by hand? ( that doesn't make sense) This credit card offers downloads in either QIF or spreadsheet. In microsoft.public.money, riskymanr wrote: >Then I download a statement from a Credit card and import it in to MS Money I >always get the Posting Date not the Transaction date. Do you mean you first enter the transactions by...

How do I convert a time format to a double
I use a function to calculate the hours worked based on time 12:00pm to 4:00pm is 4.0 hours when I get the number for hours worked it is in time format / .27398237 I need the number 4 so that I can do my calculation. How do I convert it to a number? Multiply with 24 and format as general -- Regards, Peo Sjoblom "DMB" <DMB@discussions.microsoft.com> wrote in message news:552FAD83-E8D4-4573-B9FF-613DDD259C22@microsoft.com... > I use a function to calculate the hours worked based on time > > 12:00pm to 4:00pm is 4.0 hours > > when I get the number for hou...

If Formula , Adding Days to a Date
A B 1 06/06/10 I want to make a formula that calculate as following; in Cell (B1) if the date in future the result will be [Enrolled], if not it will be Cell (A1) + 730 Days On Sat, 27 Mar 2010 02:14:01 -0700, Khalid A. Al-Otaibi <KhalidAAlOtaibi@discussions.microsoft.com> wrote: > A B >1 06/06/10 > >I want to make a formula that calculate as following; > >in Cell (B1) if the date in future the result will be [Enrolled], if not it >will be Cell (A1) + 730 Days Try this formula in cell B1: =IF(A1>TODAY(), "[En...

lookup a date from an array of date ranges if conditions are met
Please help...i need to lookup up a value on a specific date range. for example: column A: column b (from 9/01/2003 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Sounds like autofilter could be something, select the data (should have headers), do data>filter>autofilter, filter on the date column using custom and gate than or equal to and put 09/01/03 as a criteria -- Regards, Peo Sjoblom "nscanceran" <nscanceran.wj1qn@excelforum-no...

Release Date of V 4.0
Hi Does any one know the official release date for CRM V 4.0? Will CRM V 4.0 be available for partners before it is available for the general public? Thanks alot ...

Date and Time formular
I'm trying to take one date and time away from another but having a problem fathomining it out eg 01.04.2008 10:55:27 - 01.04.2008 9:37:13 The date and time are in the same cell eg 01.04.2008 10:55:27 is in A1 A formula for this would be great if anyone has one to hand Thanks Just subtract =End_Time-Start_Time -- Regards, Peo Sjoblom "SS" <Student.2@uk.bosch.com> wrote in message news:g0hgsk$jgc$1@news4.fe.internet.bosch.com... > I'm trying to take one date and time away from another but having a > problem fathomining it out > > eg >...

Date Formatting Problem
Hi I run Excel 2K I download info from the mainframe into a workbook. This information contains a date for each entry. This is the format that the "date" is downloaded in (29:53.0)....when the curser is placed on this example it displays it as 31/07/2009 12:29:53 AM in the "edit bar" of the spreadsheet. I can then format the (29:53.0) to dd/mmm/yy which then reads 31/Jul/09. However in the edit bar still shows it as 31/07/2009 12:29:53 AM. I need to be able to format it so that in the edit bar it only shows the dd/mmm/yy and not have the time format attched to i...

Date and Time #2
I see how that NOW() function works but is it possible to display only the time and not the date? Nick Use now(), then format the cell to only show time format=>Cells=>Number Tab, select time and pick a format. -- Regards, Tom Ogilvy Lord Of The Morning <Not@aol.com> wrote in message news:vmjrr6dsduv569@corp.supernews.com... > I see how that NOW() function works but is it possible to display only the > time and not the date? > Nick > > Yes, with TIME you'll get only the (current) time "Lord Of The Morning" <Not@aol.com> wrote in message...

Problem with query dates
Hi My records date range in the database is Betwene 20/05/20010 and 22/05/20010 . When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the records less than this date, OK. But if I change the month, this date to 24/04/2010 00:00:00 shows the same records. That is even if I change the month the records that appear are always less than the 24 days without following month. There lies my problem. Note: My date style is dd-mm-yyyy and time is hh: mm: ss I really need to fix this.Seabra Dim Q1, SQL As String Dim DateTime1 As Date Dim ConnString As S...

How to add time from multiple cells & date Cells & machine type c
I'm trying to add up the amount of machinery downtime from a number of different cells but the thing is that I need to only have it added up by the date set in another cell (Which can have multiple cells of the same date) as well as the specific name of that machinery in another cell(which can also have multiples of the same machinery name). How can i get to just add up for that specific date, machine name, and time. Example: Date (Cell 1A thru 50A) Machinery Name(Cell 1B thru 50B) Downtime(Cell 1J thru 50J) Please help Thanks Example:Sheet1 Data Column A ...

Problem selecting TOP values by category and date
Hello,I need to add ranking to a table of values, and even through I've gonethrough everything in the group, I can't pinpoint my problem.Here goes... I have the following resulting query Date MeasureID Store Perf----------------------------------------------------------1/1/2007 1 5 100.00%1/1/2007 1 1 100.00%1/1/2007 1 4 97.00%1/1/2007 1 2 89.00%1/1/2007 1 3 89.00%1/1/2007 2 2 100.00%1/1/2007 2 3 100.00%1...