Convert YYYY, MM, and DD as a single number string

R

rmorrison

I have YYYY, MM, and DD entered in separate cells in Excel 2007. I want to
create a new Job Number with YYYYMMDD-####, where #### is a new job which is
attached to the date string. How do I do this?
 
S

Sheeloo

Requirement not clear. Do you want YYYYMMDD-#### in a cell and you have all
the components in different cells?

Enter this in F1
=A1&B1$C1&"-"&D1 assuming you have the above in Col A-D
 
R

rmorrison

Yes, all components are in different cells and tabs within the same file. I
entered the formula below but only the test displays in the cell, not the
formula value. How do I fix this?

='Lookup Table'!A423&'Lookup Table'!A425&'Lookup Table'!A427&"-"&B4
 
R

rmorrison

The value now displays

39710.604976388939710.604976388939710.6049763889-101

It should display 20080919-0101. It doesn't seem to be working.
 
R

rmorrison

It's working now but the cell display reads:

39710.604976388939710.604976388939710.6049763889-101

It should display 20080919-0101. It doesn't seem to be working.
 
P

Peo Sjoblom

Looks like you have real dates and times in your cells

=TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1





--


Regards,


Peo Sjoblom
 
S

Sheeloo

This shoud work. Did you test this out?

Peo Sjoblom said:
Looks like you have real dates and times in your cells

=TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1





--


Regards,


Peo Sjoblom
Click to expand...
 
P

Peo Sjoblom

If you look at the numbers he/she got the first one 39710 is the serial
number for the date 09/19/08 using US format which makes me believe the
value in his first cell is not 2008 but the real date formatted as YYYY and
this part

39710.604976388939710


is equal to

9/19/2008 2:31:10 PM

so I suspect he/she might have a formula like NOW() in a cell and has
formatted it as YYYY and maybe the next cell as MM etc





--


Regards,


Peo Sjoblom
 
R

rmorrison

The formula below works nicely. I did notice that my number is not out to
the thousands place. The cell in my job number is custom formated as 0000,
so when 101 is entered, it displays 0101. This four digit number is what
should be added to my file number as 20080919-0101, but it displays as
20080919-101. How do I get the last four digits to display as 0101?

Also, because my YYYY, MM, and DD fields are based on =now() formula, I'm
assuming that every day I open the file, my file number will change. Is
there a simple way to get this to be a static number after it is first
created without the additional step of copy, paste special - values?
 
R

rmorrison

This works nicely. Thanks so much. In order to format the last string to
display out 4 digits i included the following to the formula:

=TEXT('Lookup Table'!A423,"YYYY")&TEXT('Lookup
Table'!A425,"MM")&TEXT('Lookup Table'!A427,"DD")&"-"&TEXT(B4,"0000")
 
P

Peo Sjoblom

If you have NOW() in all those cells you only need to refer to one of the
cells, no need to clutter up the formula
using all 3 cells


=TEXT('Lookup Table'!A423,"YYYYMMDD")&"-"&TEXT(B4,"0000")

will work as well




--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

convert Julian date to DD/MM/YYYY 7
Formula bar shows mm/dd/yyyy. To show dd/mm/yyyy. How? 2
How to convert MM/DD/YYYY to Month 2
Sumproduct and weekday from mm/dd/yyyy format? 3
Split date from date time cells dd/mm/yyyy hh:mm. New cell dd/mm/y 2
want formula bar date to be dd/mm/yyyy instead of mm/dd/yyyy.How? 1
Changing date to eliminate day in format mm/dd/yyyy 1
change date format from dd/mm/yyyy to mm/yyyy 2

Top