| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert YYYY, MM, and DD as a single number string
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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 "rmorrison" wrote: 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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 "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
The value now displays
39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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. "rmorrison" wrote: 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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
Looks like you have real dates and times in your cells
=TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
This shoud work. Did you test this out?
"Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
Are you addressing me?
-- Regards, Peo Sjoblom "Sheeloo" wrote in message ... This shoud work. Did you test this out? "Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
No. Morrison who posted the problem....
"Peo Sjoblom" wrote: Are you addressing me? -- Regards, Peo Sjoblom "Sheeloo" wrote in message ... This shoud work. Did you test this out? "Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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 "Sheeloo" wrote in message ... No. Morrison who posted the problem.... "Peo Sjoblom" wrote: Are you addressing me? -- Regards, Peo Sjoblom "Sheeloo" wrote in message ... This shoud work. Did you test this out? "Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
You are right....
"Peo Sjoblom" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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? "Sheeloo" wrote: This shoud work. Did you test this out? "Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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") "Sheeloo" wrote: This shoud work. Did you test this out? "Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert YYYY, MM, and DD as a single number string
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 "rmorrison" wrote in message ... 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") "Sheeloo" wrote: This shoud work. Did you test this out? "Peo Sjoblom" wrote: Looks like you have real dates and times in your cells =TEXT(A1,"YYYY")&TEXT(B1,"MM")&TEXT(C1,"DD")&" - "&D1 -- Regards, Peo Sjoblom "rmorrison" wrote in message ... The value now displays 39710.604976388939710.604976388939710.6049763889-101 It should display 20080919-0101. It doesn't seem to be working. "Sheeloo" wrote: 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 "rmorrison" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy | Excel Worksheet Functions | |||
| convert string to number | Excel Worksheet Functions | |||
| convert date mm/dd/yyyy to dd/mm/yyyy | Excel Worksheet Functions | |||
| Convert a number to a name string | Excel Worksheet Functions | |||
| convert string to number | Excel Worksheet Functions | |||
Linear Mode
