Gregorian to Julian

[zendog1960]

basically, A1 has Gregorian Date, b1 needs to be Julian based on the date given in A1.

Next question: Is there a way to have a cell set the date in itself to the pc system date but not change until prompted ot some kind of user button pushed?

First question is more important though!

 

[Mark O'Brien]

For the first question, here is a way to do it with a good description of how it works:

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

For the second question, are you open to using VBA macros?

 

[zendog1960]

YES, VBA MACROS WOULD BE GOOD.

 

[zendog1960]

that formula worked great. What would I need to do to supress the first two digits or rathe the digits that represents the year.

Example:

A1 = 15-Jun-02
B1 = =RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") which gives me = 02166

I would like it to read just 166.

 

[barry houdini]

Try

=A1-DATE(YEAR(A1),1,0)

format as general

 

[zendog1960]

That worked great. Now to the second half of my orginal question...

Is there a way to have a cell set the date in itself to the pc system date but not change until prompted or some kind of user button pushed?

 

[Mark O'Brien]

I'll describe the process of adding a button to the worksheet and creating code by assuming that you have some level of expertise. If any part is particularly confusing then just ask follow up questions:

1. Add a commandbutton to the worksheet from the Control Toolbox toolbar . (If the Control Toolbox toolbar is bot visible then go to View|Toolbars|Control Toolbox)

2. Double click on the commandbutton that you just added and a VBA code window will appear. There will already be some code that looks like this:

Private Sub CommandButton1_Click()
  
End Sub

3. Assuming that you want the date to be added to cell A1 on Sheet1, you should modify the code to look like this:

Private Sub CommandButton1_Click()
  Worksheets("Sheet1").Range("A1").Value = Now
End Sub

4. Hit the close Window button (the "X") to return to the Excel workbook

5. On the Control Toolbox toolbar hit the button called "Exit Deisn Mode"

6. When you clickn your commandbutton it will insert the date in cell A1. (mess about with the cell format settings to get the date to look the way you want it)

 

[zendog1960]

That should work. Thanks for all your help. I have post another question and maybe you can help since you seem to have a great knowledge of excel.

it is about using information from one excel file in another excel file.