Gregorian to Julian

zendog1960

Active Member
Joined
Sep 27, 2003
Messages
459
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,869
Messages
6,104,474
Members
447,910
Latest member
abrook1308

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top