Getting detailed Julian date

[Steve C]

I am trying to create a spreadsheet to log some drawings that are done. I
would also like to use the julian date as a reference number. I have tried to
use the function found here, http://www.cpearson.com/excel/jdates.htm, but
that doesn't give me enough detail. Since I need multiple reference numbers
during the day, that function, which only gives me the year and the day,
limits my numbering scheme.

Can anyone help?

Thanks in advance!

 

[dlw]

if you format a column date and time, then enter, say 01/06/08 10:30 AM, then
do the VALUE of that cell, you will get a decimal number that represents the
date and time. That could be your reference number.

 

[Steve C]

I tried that, and ended up only getting the year and day format, no
hours/minutes, etc. Do you know if there is something missing from the
original function that was listed on the website? I'm not sure if using that
will allow the return to be anything more than just the year and day.

Thanks for your reply!

 

[Roger Govier]

Hi Steve

You could modify Chip's formula to
=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")&TEXT(MOD(A1,1)*1440,"0000")

You will get the Julian date with a number following it that will range from
0000 to 1440 representing each minute of the day.

 

[Steve C]

Excellent, just the return I was looking for!

Many thanks!

 

[Tyro]

Why not just get the Julian date, append a 1 to it and manually add 1 every
time you use it? For example, using Chip's formula, 1/6/2008 is 08006. You
copy that and paste special/value into a cell so that you have a number not
a formula and append 1 to make it 080061 (or 0800601, 08006001 etc). Each
time you use the number, you increment it by 1 manually, and so after using
080061, it becomes 080062 for the next use. The problem with formulas
involving date and time functions is that they are volatile - that is they
change every time anything is calculated on the worksheet. It sounds to me
like you want numbers that change only when you use them.

tyro

 

[Steve C]

That's also a thought. What I was trying to do is create a way to hand out
drawing numbers. I know I should probably be using Access, but I'm not
familiar with that at all, and thought I could give it a go in Excel.

Obviously I'm not a whiz kid in Excel either, but at least I'm familiar with
it, and "ain't skeered". ;-)

Thanks!

 

[Tyro]

I don't see how Access would help you. Excel is much better suited for
generating unique numbers. If you only need a few, it's hardly worth going
to great lengths to get them. Sometimes manual is better. You could also
make a macro to update your number by one so that you have only to press a
hot key.

tyro

 

[Steve C]

Depending on the level of detail I want/need, plus doing tracking, I thought
Access would be the way to go on something like this. After all, what I am
doing is building a database. Right now, it's a database in Excel. Since I
might add more data fields later, I had considered trying to learn enough in
Access to get me started there, but still use the Excel as a starting point.

 

[Tyro]

Access is a great way to build the structure of a data base. But when it
comes to entering data and validating it programming is almost a must. Also
programming is required for many other things in Access. I've never seen a
real-world Access data base (or any other data base) that didn't have a lot
of programming. Good luck.

tyro

 

[Steve C]

I know, that's why I wanted to do it in Excel first. I don't mind learning
the process, I just wish I had more time available.

Thanks for your help!