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!

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" wrote:

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!

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!

"dlw" wrote:

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" wrote:

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!

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,"000 0")

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

--

Regards
Roger Govier

"Steve C" wrote in message
...
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!

"dlw" wrote:

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" wrote:

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!

Excellent, just the return I was looking for!

Many thanks!




"Roger Govier" wrote:

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,"000 0")

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

--

Regards
Roger Govier

"Steve C" wrote in message
...
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!

"dlw" wrote:

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" wrote:

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!

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" <Steve wrote in message
...
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!

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" wrote:

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" <Steve wrote in message
...
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!

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" wrote in message
...
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" wrote:

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" <Steve wrote in message
...
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!

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" wrote:

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" wrote in message
...
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" wrote:

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" <Steve wrote in message
...
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!

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" <Steve wrote in message
...
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!

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!



"Tyro" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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" <Steve wrote in message
...
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!