julian date #3

Is there a formula or line of code that, in a date cell, would automatically 
convert a date entered in the 07/16/04 format into the 04188 julian format?

thank you. 
0
geza (10)
8/17/2004 9:37:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
864 Views

Similar Articles

[PageSpeed] 54

You can't have a formula in a cell and make an entry in it -  the 
formula is overwritten. You can use an event macro:

Put this in the worksheet code module (right-click the worksheet tab and 
choose View Code):

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Dim nYear As Long
        Dim nDays As Long
        With Target
           If .Count > 1 Then Exit Sub
           If Not Intersect(.Cells, Range("A1")) Is Nothing Then
              If IsDate(.Value) Then
                 nYear = Year(.Value)
                 nDays = CDate(.Value) - DateSerial(nYear, 1, 0)
                 .NumberFormat = "@"
                 Application.EnableEvents = False
                 .Value = Format((nYear Mod 100) * 1000 + nDays, "00000")
                 Application.EnableEvents = True
              End If
           End If
        End With
    End Sub


This will produce pseudo-Julian dates (for real Julian dates, see

    http://aa.usno.navy.mil/data/docs/JulianDate.html

)


In article <FCB141ED-BAF6-4A19-9DF2-D34CC2F81616@microsoft.com>,
 "geza" <geza@discussions.microsoft.com> wrote:

> Is there a formula or line of code that, in a date cell, would automatically 
> convert a date entered in the 07/16/04 format into the 04188 julian format?
> 
> thank you.
0
jemcgimpsey (6723)
8/17/2004 10:40:35 PM
Reply:

Similar Artilces:

Outlook Rules Wizard #3
Have a question regarding e-mail receipts. I have applied rules wizard to screen unwanted e-mails. I have entered the word, such as "SPAM", to be screened. If found in the subject or body of the e-mail, it is to be deleted from the ISP server before it is ever downloaded to my computer. Occassionally I will get the message, "You don't have appropriate permission to perform this operation", and the message will get through. I will then go in rules wizard and my rule box will be unchecked. I will have to re-check it to re- activate the rule. How can I make...

MATCH formula #3
Thanks. I'll try out those suggestions. De -- da ----------------------------------------------------------------------- dah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=649 View this thread: http://www.excelforum.com/showthread.php?threadid=27191 ...

Date formats
Hi. A text box bound to a DateField has yyyy\.mm\.dd as Format. Data is displayed correctly but while entering it (apparently) reverts to the computer date format, messing everybody's mind. Is there a way to keep entering and showing date format to yyyy\.mm \.dd? Thanks Henry You need to use an input mask. They are described in Acccess Help system. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "H. Martins" wrote: > Hi. > > A text box bound to a DateField has yyyy\.mm\.d...

Last Balanced Date
Does anyone know a quick way to get a list of accounts and the last date they were balanced? I'm finding myself having to select each account and manually check to see if it is up to date. I would think there would be a report of some kind that would display this somewhere, but I can't seem to find one. Any suggestions? Thanks in advance...Skip Skip, I've been thinking of the same exact question ever since I made the upgrade from Money 2004 to 2005. The ability to see the last balanced date across all accounts did exist in the 2004 version, and it was very useful. It...

Data from .txt file being read in as date, and should be text
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

sequential date related question
Hi - If for example within cell A3 the date of 09/03/2001 was entered would it be possible in the cell beneath it, A4, to write code tha would break the date down into parts (e.g., Month, Date and Year) an to modify these parts individually? Therefore within cell A4 the code would suggest that it equaled A3 bu that it would have the potentail to modify its parts (e.g., Month, Dat and Year) and not as a whole (e.g., A3+365) Thus what I would like to place in cell A4 is code that would b somehow similar to the following =IF(MONTH(A3)<>12,DATE(YEAR(A3),MONTH(A3)+1,1),DATE(YEAR(A3)+1,1,1))...

Internet Explorer 6.0 Sp1 Component Update 3.0 for Windows 98
For those of you that want to use various Win-2K updates from Microsoft and apply them to your Win-98 system, this update package has been designed to bring together all those various updates and install them automatically. MD InternetExplorer 6.0sp1 Component Update 3.0 *Windows Script Update 5.6 *971961 - Unofficial JScript Security Update *944338 - Unofficial Windows Script Security Update *973354 - Unofficial Outloook Express Cumulative Security Update *976325 - Unofficial Internet Explorer Cumulative Security Update (with uninstall) *905495 - Unofficial Security Update (MSI...

date formatting changes
I have a VB macro that changes all date formats to dd/mm/yy. This works fine. However, sometimes when I go back into my files at a later date, I find that the dates convert back to dd/mm/yyyy. Why does this happen when I have specifically set the format to dd/mm/yy? There are some custom date formats that don't belong to you. I'm betting that you happened to use the same format that excel uses to tell it to pick up the format from the windows regional settings (under control panel). xl2002 is more honest with the way it deals with dates. (I think it's new with xl2002, but ma...

POP 3 CONNECTOR #2
Hi Exchange 5.5, which was setup years back with POP3 connector cuz there was no internet on site. Now I want to setup MX record pointing to this server. Do I simply stop running/uninstall POP3 Connector service and make sure SMTP is running? Or there is more than that? TIA In news:102F1BFB-EEAD-48B4-A5A1-2E03A867B255@microsoft.com, Kelly <Kelly@discussions.microsoft.com> typed: > Hi > > Exchange 5.5, which was setup years back with POP3 connector cuz > there was no internet on site. Now I want to setup MX record pointing > to this server. Do I simply stop running/...

3 format cell tabs hidden in excel dialog box
When I right click on the cell and go to format cells instead of seeing 6 tabs as usual I see three tabs and the other three are on a lower level and covered by the information in the dialog box. I can only see the very top edge of the tabs. Any ideas what could be causing this? If you're using Excel 97, and making copies of copies of worksheets, you may hit a limit, because of the way the sheets are being numbered in the background. If that's the case, try copying one of the first sheets, instead of the last one. pjbjamison wrote: > When I right click on the cell and g...

Rules Error #3
I keep getting this dialog box: Rules in Error Rule: Server-Requested Client Action Error: MoveCopy, Unable to create dest msg. I've checked my rules, and they all work. This only comes up when starting Outlook. What's going on here? ...

Excel database update #3
Hi, Have an excel template that should send data into a excel database. When I run a marco, the database does not automatically update, but if i do it step by step it updates. ("MatTer1.xls") when saved should update the database, by creating a new record in the code hereunder ActiveWindow.WindowState = xlMinimized Windows("CHAVES.xls").Activate ActiveWindow.WindowState = xlMaximized Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.WindowState = xlMinimized ...

Date and time stamp
I would like to place a time and date in a cell that is only update when the cell that it is linked to is updated in any way. I have trie today() and now() but that updates anytime I do anything anywhere o the spreadsheet. I am using this as a log and as such I want to keep "time stamp" when someone enters their initials. Thanks, Troosh:confused -- Message posted from http://www.ExcelForum.com For a possible solution see http://www.mcgimpsey.com/excel/timestamp.html -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "troosh >&...

Upgrading MS CRM 3.0 to MS CRM 4.0
We have an MS CRM 3.0 setup and running well for quite long time. We would like to upgrade it to MS CRM 4.0. Current setup is, 1. Lot of reports added & customized for user needs 2. Lot of entities added & customized for user needs 3. Lot of forms added & customized for user needs 4. Lot of custom developed pages added (developed in ASP.NET 1.1). Of course it's running on separate site but integrated inside the MS CRM 3.0 (so typically used MS CRM 3.0 service calls) 5. Lot of Stored Procedure's & View's added 6. Customized the settings based on user needs My do...

Dynamics Utilities Fails when upgrading to GP 9 Service Pack 3
We have a client that is attempting to load GP9 service pack 3. After the install is completed, the machine was rebooted and the dynamics utilites was ran. While utilities is running (the window states "Server Installation Progress"), an error message pops up stating "File Not Found C:\Program Files\Microsoft Dynamics\GP\SQL\Upgrade\2277\9.0.282\Driver.ini" After this error utilities does not complete and the install fails. I have looked at the directory on this machine and a 9.0.282 folder does not exist. Why is the upgrade looking for it and how can we proceed ...

custom date format mmm/yy
Hi, is there any way in a Report to customize the date displayed in the report to be mmm/yy? Or even mmm/yyyy would be better for me than the options Access seems to offer in their reports. All I see there is Short Date, Medium Date, Long date - they all take up so much space in a densely packed report. Thanks, Harold You can use your own formats In a control's format property, enter mmm/yy or mmm/yyyy or mm/yy For a discusson of the formatting characters, put your cursor in the format property and press the F1 key for help. -- John Spencer Access MVP 2002-2005, 2007 Cent...

Date in Report/Query
Hi. I have a report that has worked up until this past week. The query behind the report has the following expression: DateAuth: Format([CredAuthDate],"mm/dd/yy") If I type in 12/31/09....report works fine. Type in anything with "10" as the year and it fails. Any suggestions would be greatly appreciated. If I take off the format for the field it works. Is something different for the year 2010? This is an Access 2003 database. Thanks... I suggest applying criteria to an 'unformatted' field and turn off the display by unchecking the 'Sh...

How can I get yesterdays date in a custom footer &[DATE]-1 does .
How can I get yesterdays date in a custom footer &[DATE]-1 does not work try =today()-1 chuck <chuck@discussions.microsoft.com> wrote in message news:5A51D4C3-07A5-48A8-A594-09F51D4B46A6@microsoft.com... > How can I get yesterdays date in a custom footer &[DATE]-1 does not work Chuck - You will have to manually or programmatically change the displayed date in the footer. There are no page setup codes that will put a different date in a footer. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Standard Reports on CRM 3.0
I am trying to setup CRM 3.0; I have set it up on sql 2005 and migrated the data from ACT with no errors. when I click on reports in workplace a message is displayed - an error has occurred - contact the system administrator. I ran the environment diagnostic tool and no errors appeared. Can anybody point me in the right direction to resolve this. For more detail error information you can turn on Dev Errors. To do this, log onto the Microsoft CRM Server. Open a File Explorer window and navigate to the folder where the Microsoft CRM web site is installed. Open the web.config and chang...

smtp routing #3
I currenrly run a front end back end setup on exchange 2003. currently all mail comes directly into the backend. however I want a setup so that mail will go to the front end or another box then forwarded to the backend. how do I do this and is this a more secure setup or configuration? thanks On Sun, 13 Nov 2005 14:12:04 -0800, "tony" <none@none.com> wrote: >I currenrly run a front end back end setup on exchange 2003. currently all >mail comes directly into the backend. however I want a setup so that mail >will go to the front end or another box then forwa...

Microsoft Dynamics CRM 3.0 Implementation For Large Corporation (August 2006) #4
Microsoft Dynamics CRM 3.0 Implementation For Large Corporation (August 2006) How could contribute and be a part of its computer environment.(Sales Module,MS CRM Security,Integration with Microsoft Dynamics GP 9.0/Microsoft Great Plains,with IBM Lotus Notes Domino,International Considerations,Competition) http://microsoft-crm-3.blogspot.com/2006/08/microsoft-dynamics-crm-30.html ...

Date trasfer when importing emails to CRM
When I import old emails into CRM from Outlook they are tagged in CRM with the today's date, the day I imported them. Is there any way to alter this date once in CRM to reflect the actual date of the email? I believe your referring to promoting emails within Outlook. If so, please see this thread: news:5DF7B2F7-B5CE-4FF9-80E3-421D4FE5F500@microsoft.com -- Jason -- This posting is provided "AS IS" with no warranties, and confers no rights. "Eric" <Eric@discussions.microsoft.com> wrote in message news:6CF94DF6-7659-4793-AF6E-439BB480DC29@microsoft.com......

EXCEL 2007 Formula to calculate INTEREST only on a 3 month bridge
I am trying to calculate monthly INTEREST ONLY payment on a short term bridge/swing loan. Assuming an interest rate of 2.75%, paid monthly, Also assuming it will be required for a 3-6 month period, amount approx $500,000. Just switched to Excel 2007 but don't seem to be able to calculate using the formula builder. Not sure if it is compounded daily or monthly. You might want to check out the IPMT function. From the XL help file: IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must...

PO Last Receipt Date
If an item has been received on a PO, when you select the PO in Purchase Order Entry, you can go into the Purchasing Item Detail Entry window and see a Last Receipt date for the item that has been received. Is there any way to print this date on the PO printout? Elaine, Easily. This field is, not suprisingly, "Last Receipt Date" and it is the PO Line table. If it isn't already on your report, you should be able to drag it onto the report as long as it's not one of the temp table based reports. And if it is, you may then be able to link the po line table in and th...

CRM 3.0 Implementation
I am interested in the experiences of others with implementing Microsoft CRM 3.0. I am a one man development team who has been tasked with implementing CRM 3.0 with 30 users initially. Our organization has been running on Lotus Notes for quite a while. We moved to echange for e-mail over a year ago but still use Lotus for custom databases. The first step will be pulling the data from Lotus Notes to CRM. I have looked into the Microsoft CRM 3.0 Certification. There is a company that offers a 10 day CRM 3.0 boot camp. Is this a good idea, and at what point should I take it? We would lik...