Gregorian to Julian

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
449
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!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
449
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

Try

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

format as general
 

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
449
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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:

Code:
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:

Code:
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

Active Member
Joined
Sep 27, 2003
Messages
449
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,649
Members
412,335
Latest member
cinciri99
Top