Topics Topics Help/Instructions Help Edit Profile Profile Member List Register Paatha Gnyapakaalu - Archives from Old DB  
Search New Posts 1 | 2 | 8 Hours Search New Posts 1 | 3 | 7 Days Search Search Tree View Tree View Latest tweets Live Tweets   Hide Images
Bewarse Talk Discussion Board * Archives - 2016 * Cine Talk - Reviews, Gossips, Insider Info etc. * Archive through February 19, 2016 * Any Excel experts here? < Previous Next >

Author Message
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1481
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 9:43 am:    Edit Post Delete Post Print Post
Nalla_baalu:

Annai vachindha



yes.Blazewada cheppindi perfect gaa work ayyindi.Nene oka step lo confuse ayyaanu.
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1480
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 9:42 am:    Edit Post Delete Post Print Post
Blazewada:

E1: YEAR_SPLIT D1: MONTH_SPLIT B1: DATE_REPLACE (new value) =DATE(E1,D1,B1)



OK. Got it. Thanks so much.
CLIPART--band2
Top of pagePrevious messageNext messageBottom of page Link to this message

Nalla_baalu
Censor Bewarse
Username: Nalla_baalu

Post Number: 124735
Registered: 06-2011
Posted From: 166.137.244.29
Posted on Friday, February 19, 2016 - 9:39 am:    Edit Post Delete Post Print Post
Annai vachindha
Aug 11th 2011 weight -- 84 kg; Aug 11th 2012 weight -- 88 kg; Aug 11th 2013 weight -- 80 kg; July 2015 -- 90 kilos (5'10")
Top of pagePrevious messageNext messageBottom of page Link to this message

Blazewada
Celebrity Bewarse
Username: Blazewada

Post Number: 25900
Registered: 08-2008
Posted From: 58.182.87.144
Posted on Friday, February 19, 2016 - 9:29 am:    Edit Post Delete Post Print Post
Kalikaalam:


a qq: after splitting the date , there are 3 columns one each for date, month and year. How did you replace the date value with the values from column_A?




E1: YEAR_SPLIT
D1: MONTH_SPLIT
B1: DATE_REPLACE (new value)

=DATE(E1,D1,B1)
जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1477
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 9:28 am:    Edit Post Delete Post Print Post
Blazewada:

if u dont mind send it to blazewada . 007 gmail

Blazewada:

if u dont mind send it to blazewada . 007 gmail



I can not send from my work computer.

a qq: after splitting the date , there are 3 columns one each for date, month and year. How did you replace the date value with the values from column_A? Once I know that, think I can covert it back to date format.
Top of pagePrevious messageNext messageBottom of page Link to this message

Blazewada
Celebrity Bewarse
Username: Blazewada

Post Number: 25895
Registered: 08-2008
Posted From: 58.182.87.144
Posted on Friday, February 19, 2016 - 9:20 am:    Edit Post Delete Post Print Post
Kalikaalam:



annai nenu esina tabular form enaa mee esindi?
replace kakunda DATE function vadandi join cheyaneeki
naa last post lo

if u dont mind send it to blazewada . 007 gmail
जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1476
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 9:17 am:    Edit Post Delete Post Print Post
I was able to split right away. But replace/substitute functions are not working for me.CLIPART--sad
Top of pagePrevious messageNext messageBottom of page Link to this message

Blazewada
Celebrity Bewarse
Username: Blazewada

Post Number: 25892
Registered: 08-2008
Posted From: 58.182.87.144
Posted on Friday, February 19, 2016 - 9:05 am:    Edit Post Delete Post Print Post
OLD_DATE---------------DATE_REPLACE---- MONTH_SPLIT-----YEAR_SPLIT------ NEW DATE
23 December, 2019-----12-------------------------12----------------2019--------------- 12 December, 2019
24 December, 2019 -----13-------------------------12----------------2019--------------- 13 December, 2019
25 December, 2019 -----24-------------------------12----------------2019--------------- 24 December, 2019



E1: YEAR_SPLIT
D1: MONTH_SPLIT
B1: DATE_REPLACE

=DATE(E1,D1,B1)
जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1475
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 8:45 am:    Edit Post Delete Post Print Post
Blazewada:

done work out aindi split and join excel lo



Just a minute. Try chesthunnaa..
Top of pagePrevious messageNext messageBottom of page Link to this message

Blazewada
Celebrity Bewarse
Username: Blazewada

Post Number: 25891
Registered: 08-2008
Posted From: 58.182.87.144
Posted on Friday, February 19, 2016 - 8:44 am:    Edit Post Delete Post Print Post
In your Excel worksheet, select a column of text entries you want to convert to dates.

Switch to the Data tab, Data Tools group, and click Text to Columns.

https://cdn.ablebits.com/_img-blog/text-date/excel-text-to-columns.png

In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.


https://cdn.ablebits.com/_img-blog/text-date/excel-convert-text-date.png


In step 2 of the wizard, uncheck all delimiter boxes and click Next.

https://cdn.ablebits.com/_img-blog/text-date/excel-convert-text-date2.png

In the final step, select Date under Column data format, choose the format corresponding to your dates, and click Finish.
In this example, we are converting the text dates formatted as "01 02 2015" (month day year), so we select MDY from the drop down box.

https://cdn.ablebits.com/_img-blog/text-date/excel-convert-text-date3.png


Finally, you have to combine the date parts together by using a DATE formula. The syntax of the Excel DATE function is self-explanatory:
=DATE(year, month, day)

In our case, <code>year</code> is in column E and <code>day</code> is in column D, no problem with these.

It's not so easy with <code>month</code> because it is text while the DATE function needs a number. Luckily, Microsoft Excel provides a special MONTH function that can change a month's name to a month's number:

=MONTH(serial_number)

For the MONTH function to understand it deals with a date, we put it like this:

=MONTH(1&C2)

Where C2 contains the name of the month, January in our case. "1&" is added to concatenate a date (1 January) so that the MONTH function can convert it to the corresponding month number.

And now, let's embed the MONTH function into the <code>month</code> argument of our DATE formula:

=DATE(F2,MONTH(1&D2),E2)

And voila, our complex text strings are successfully converted to dates:

https://cdn.ablebits.com/_img-blog/text-date/convert-text-strings-dates-result.p ng
जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के
Top of pagePrevious messageNext messageBottom of page Link to this message

Proofdada
Bewarse Legend
Username: Proofdada

Post Number: 145436
Registered: 03-2004
Posted From: 71.170.93.71
Posted on Friday, February 19, 2016 - 8:41 am:    Edit Post Delete Post Print Post
Blazewada:

1000 usd adugu kali annay nu...
Top of pagePrevious messageNext messageBottom of page Link to this message

Blazewada
Celebrity Bewarse
Username: Blazewada

Post Number: 25890
Registered: 08-2008
Posted From: 58.182.87.144
Posted on Friday, February 19, 2016 - 8:40 am:    Edit Post Delete Post Print Post
Kalikaalam:



done work out aindi split and join excel lo
जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के
Top of pagePrevious messageNext messageBottom of page Link to this message

Proofdada
Bewarse Legend
Username: Proofdada

Post Number: 145435
Registered: 03-2004
Posted From: 71.170.93.71
Posted on Friday, February 19, 2016 - 8:38 am:    Edit Post Delete Post Print Post
Kalikaalam:

Columna is number/numeric
Column_B is character.

obviously,colun_C(the output that I am going to get) is also character.

macro rayali anukunta... neenu rayagalanu but am busy... lepothe sett bha rasesthadu...
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1474
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 8:37 am:    Edit Post Delete Post Print Post
Gaali:

What are the data types for the two columns?



Columna is number/numeric
Column_B is character.

obviously,colun_C(the output that I am going to get) is also character.
Top of pagePrevious messageNext messageBottom of page Link to this message

Musicfan
Bewarse Legend
Username: Musicfan

Post Number: 57583
Registered: 05-2004
Posted From: 66.117.193.162
Posted on Friday, February 19, 2016 - 8:36 am:    Edit Post Delete Post Print Post
concatenate will work...
Nannaku Premato Audio Review
Top of pagePrevious messageNext messageBottom of page Link to this message

Proofdada
Bewarse Legend
Username: Proofdada

Post Number: 145434
Registered: 03-2004
Posted From: 71.170.93.71
Posted on Friday, February 19, 2016 - 8:36 am:    Edit Post Delete Post Print Post
=Date( Year(columnB),Month(ColumnB),$(ColumnA1))

if columnB is in Dateformat(it must be as excel takes it if you write like that as you specified)...

try it...
Top of pagePrevious messageNext messageBottom of page Link to this message

Blazewada
Celebrity Bewarse
Username: Blazewada

Post Number: 25889
Registered: 08-2008
Posted From: 58.182.87.144
Posted on Friday, February 19, 2016 - 8:32 am:    Edit Post Delete Post Print Post
Kalikaalam:



https://cdn.ablebits.com/_img-blog/text-date/convert-text-strings-dates-result.p ng

split and join tho avuddi.

https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/

indulo 5 & 6 points choodandi
जिसको ढूंढे बाहर बाहर - वो बैठा है भीतर छुप के
Top of pagePrevious messageNext messageBottom of page Link to this message

Gaali
Celebrity Bewarse
Username: Gaali

Post Number: 45023
Registered: 03-2004
Posted From: 131.247.54.65
Posted on Friday, February 19, 2016 - 8:30 am:    Edit Post Delete Post Print Post
What are the data types for the two columns?
Be Positive!
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1473
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 8:28 am:    Edit Post Delete Post Print Post
..or any SQL function for this? .Filed_A got number value and Field_B got Char/date value. Rest is same to same like in the previous post..
Top of pagePrevious messageNext messageBottom of page Link to this message

Gaali
Celebrity Bewarse
Username: Gaali

Post Number: 45021
Registered: 03-2004
Posted From: 131.247.54.65
Posted on Friday, February 19, 2016 - 8:26 am:    Edit Post Delete Post Print Post
Calling Nalla Balu CLIPART--big_wave
Be Positive!
Top of pagePrevious messageNext messageBottom of page Link to this message

Kalikaalam
Kurra Bewarse
Username: Kalikaalam

Post Number: 1472
Registered: 04-2015
Posted From: 171.161.160.10
Posted on Friday, February 19, 2016 - 8:23 am:    Edit Post Delete Post Print Post
I have 2 columns.
Column_A got numbers like 20
Column_B got date like 10Jan2016.

I want to replace the date part of the column_B with column_A. In the above example, I want to see 20JAn2016.

As there thousands of rows, I am looking if there is any formula/automation technique to do that. Right now, I am googling, but if anybody knows any technique, please share with me.

Topics | Last Hour | Last Day | Last Week | Tree View | Search | Help/Instructions | Program Credits Administration