• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populating a Date column based on another colum

H

haznavy

New Member
****Warning EXCEL Newbie*****


Here's one for the Ninja's out there. I have a worksheet that I need to populate col. A by the date input in col. S. Caveat: there may be multiple entries for any particular day. Therefore, I need col. A to reflect 120214, 120214.1, and 1202014.2 etc. but can't figure out how to do it. Any help would be greatly appreciated. Thank you.
 
Hi Haznavy,


You mean in Column A are your Dates, in Column S you want dates with count i.e. 12.02.14.01 and so on?


If your dates are located in column A (12.02.14)and want your result to be displayed in column B like this 12.02.14.01 use this one (hopefully it will work)


=DAY(A1)&"."&MONTH(A1)&"."&YEAR(A1)&"."&COUNTIF($A$1:A1,A1)

... and drag it down..Here is a sample file.


http://www.4shared.com/file/ZOJobLO_/haznavy.html


Regards,

FASEEH
 
An even simpler could be if you replace entire first part that generates date for you with just A1 i.e. (havent tried so give it one)


=A1&"."&countif($A$1:A1,A1)


which ever u like...

Faseeh
 
Faseesh,


I know it sounds convoluted, but the date data is in "S" and needs to populate "A" which will become the control #. So if S2 = 120214 --> A2 = S2 and if S3 = 120214 -->a3 = 120213.01 and so on.
 
In case ur data is located in ColumnS and that u want to put it ColumnA simply replace A's in the formula with S in the first formula and enter this formula in ColumnA.... Have u downloaded my file...I think it will be easier if u download this file.. :)


Faseeh
 
Faseeh,


Tried to dl your file, but am prohibited by command firewall. Utilizing the following adaptation of your formula:


=12&Month(S56)&Day(S56)&"."&Countif($S$56:S56,S56) produces 12214.1


So very close, but two issues. I need it to populte the date in yymmdd format and NOT affix a .1 to the first entry for a specific date....should look like this 120214 for the first entry and 120214.1 etc for subsequent entries on the same date.


I have tried formatting col. A and S to yymmdd and changing the formula to =S56&"."&Countif($S$56:S56,S56) which produces 40953.1 which I think is the DATEVALUE for col. S for 120214.1. Nothing I do seems to be the right fix.
 
Hi haznavy,


Use this formula:


=YEAR(S16)&"."&MONTH(S16)&"."&DAY(S16)&IF((COUNTIF($S$1:S16,S16)-1)=0,"","."&(COUNTIF($S$1:S16,S16)-1))


- It will not show any thing with any date encountered first time, for second time will add .1 to it.

- Is formatted YY.MM.DD


...here is a sample file for download. hope it works as it is supposed to. :p

http://dl.dropbox.com/u/60644346/haznavy_1.1.xlsx


Faseeh
 
Faseeh,


Able to manipulate the formula to achieve all goals, including display in 120217 format, except the .1, .2 at the end using:


=12&"0"&MONTH(S4)&IF((DAY(S4)>=10),DAY(S4),0&IF((DAY(S4)<=10),DAY(S4)&IF((COUNTIF($S$1:S4,S4)-1)=0,"","."&(COUNTIF($S$1:S4,S4)-1)))) --> 120110


=12&"0"&MONTH(S5)&IF((DAY(S5)>=10),DAY(S5),0&IF((DAY(S5)<=10),DAY(S5)&IF((COUNTIF($S$1:S5,S5)-1)=0,"","."&(COUNTIF($S$1:S5,S5)-1)))) --> 120110 [.1 didn't populate] :(


I'm so close....HELP. Thanks.
 
Hi haznavy,


Have you download my last uploaded file?? It displays dates as you have asked following is the result that it shows:


1900.1.13

1900.1.13.1

1900.1.13.2

1900.1.13.3

1900.1.13.4

1900.1.14

1900.1.13.5

1900.1.14.1

1900.1.15

1900.1.15.1

1900.1.17

1900.1.17.1

1900.1.17.2

1900.1.17.3

1900.1.21

1900.1.22


You said in your last post that there should not display any .1 when the date is ecountered for the first time, from second time it should add suffix .1, .2... it is the same as you said.


Here is the file: http://dl.dropbox.com/u/60644346/haznavy_1.1.xlsx


Faseeh
 
Faseeh,


Refer to yesterdays post regarding file download....I wish I could, but I am prevented by firewall protocols. Will try a workaround via my iPhone. Thanks.
 
Why??? What went wrong now?? Can u mail me the file at faseeh10@hotmail.com ? So that i can adjust and revert it to you??? That file is working just fine here!


Faseeh
 
You must log in or register to reply here.
Back
Top