julian dates...again!

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94
Hey all,

I've spent some time searching through these boards and I've also taken a look at cpearsons site, but can't seem to get this figured out!

I have a userform with two sets of three combo boxes. The first set of boxes is used to capture date number 1 (cmbo 1 = dd combo 2 = mm combo 3 = yyyy) and the second to capture date number two.

I then concatenate the results (adding forward slashes) to give me the 'proper' dates eg 01/01/2005

I am using datediff to compare the number of months between these dates.

If a user enters a date that is prior to the comparison, I get an error.

I thought that if i first converted the dates to julian, I could check to see if the comparison was valid, and, if not, serve an error message.

For some reason, I just can't seem to get my dates converted to julian!

I looked at cpearsons function - but couldn't work out how I was supposed to embed it in my code. So then thought i could use forumlar1c1 and use one of the formulas (found on these boards) to populate an arbitrary cell with the julian value which i could then use to make a comparison. for some reason I can't get this to work either!

can anyone help?

In a nutshell i want to take a date (that is held in a variable in the format 01/01/2005) and convert it into julian.

Thanks so much...!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Dates! Arrgghh! I think thety get us all - and must be the most popular threads on this board - so dont feel bad!

I am not sure what od chips you are refering to - but have you checked out the "isdate" function and the "datevalue" function. If memory serves me correctly these are intrisically linked to your regional settings - so make sure you have this set correctly.
 
Upvote 0
macleanb said:
but have you checked out the "isdate" function and the "datevalue" function. If memory serves me correctly these are intrisically linked to your regional settings - so make sure you have this set correctly.

I didn't think either of these functions could convert to Julian...?

(thanks for the reply!)
 
Upvote 0
isdate will check if it can - datevalue does the actual conversion (from text)
 
Upvote 0
What exactly are you trying to do?

What exactly do you mean by converting the date to Julian?

As Ben has pointed out you can use various functions in VBA to deal with dates.

If you already have some code it might be worthwhile posting it.
 
Upvote 0
sorry guys...I'll try again!

I have two dates.

Date 1 is the "start date" (dd/mm/yyyy)
Date 2 is the "End date" (dd/mm/yyyy)

date 2 cannot be before date 1. The only way I could think of checking this is to convert them both to julian format and comparing them.

therefore I would like to convert a date which is currently in format dd/mm/yyyy into the julian date format.

THANKS!
 
Upvote 0
yep - thats what datevalue will do (but see remark re rgional settings - your PC neds to know whether your in DD/MM or MM/DD land)
 
Upvote 0
What do you mean by the 'julian format'?

As far as I know what you have is in Julian format.

Ar eyou having a problem with the DateDiff function?
 
Upvote 0
OK - i think I have it, but to be honest I'm not sure that this is the way you intended me to do it!!!


1. I set the value of cell A1 to my variable value (01/01/2005)

2. In cell B1 I used the formula =TEXT(A1,"yyyy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")

3. Repeat for my second date

Is this what you meant?

Thanks so much,

noddingdino!
 
Upvote 0
Norie said:
What do you mean by the 'julian format'? As far as I know what you have is in Julian format

I have a date in dd/mm/yyyy format (01/01/2005) I would like to change it to julian format (2005001)


Norie said:
Ar eyou having a problem with the DateDiff function?

Kind of! I wanted to give an error message if the user entered an 'End date' that was before the 'Start Date' It looks as though datediff can't cope with this scenario...
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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