• 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.

EXTRACTING MONTH AND YEAR TOGETHER FROM A GIVEN DATE (DD/MM/YYYY)

V

VDS

Member
Dear All,

I have a worksheet containing following : -

Sr No. == Date== Bank Guarantee No.===Amount (Rs)==Expiry Date==Month==Name of project
1==12/11/2010==0992611BG0000220==7512000 =27/12/2013== ( )==HSCL Chandigarh
2==29/03/2011==0992611BG0000050=1250000=01/01/2014== ( )==RITES LIMITED
3. ==09/05/2009==0992611BG0001781==12169860=04/03/2014=( ) Parsvnath Buildwell Pvt. Ltd.

Suppose the "Expiry Date"of Bank Guarantee No 0992611BG0000220 is on "27/12/2013", "Month" Column should display "December, 2013" together in order to take the monthly reports.

Please give the solution.

VDS
 
@VDS

Try this, say your Expiry date column in E and month column in F

then try in F2

Code: 
=TEXT(E2,"MMMM, YYYY")

Hope it clear other wise please give some more details

Thanks
 
Somendra Misra said:
Hi VDS,
Please check the attached file.IS this what you want?

Somendra.
Click to expand...

@Patnaik :
The solution provided by you is Okay.
@ Somendra : When I try to enter your formula = Date (Year (B84), Month (B84),1) it gives the message :
01/01/2005. Sir, file cannot be uploaded due to some server settings. What may be reasons.


VDS
 
sgmpatnaik said:
@vds

that is not in date format it must be in text format please check the Range B84
Click to expand...

Hai @Patnaik, @Somendra

Any way, I have copied the data thru Edit - paste special - of worksheet provided by you and made a master copy.
Now this is working nicely.

Thanks for your support.

VDS
 
@VDS

That is not a formula, what faseeh explained you select the Range which you want to the Date format as MMMM, YYYY

Say your range is $B2:$B1000
select the range
mouse right click
format cells
Custom
in Type select the format Desire

Thanks
 
sgmpatnaik said:
@VDS

That is not a formula, what faseeh explained you select the Range which you want to the Date format as MMMM, YYYY

Say your range is $B2:$B1000
select the range
mouse right click
format cells
Custom
in Type select the format Desire

Thanks
Click to expand...

@ Dear All
Thanks too much. It is so simple. When we learn something from academically, it is not complete. Like "Suppose this is a test tube". Perfection is possible only when we face practically.

Keep up good spirit.


VDS
 
VDS said:
@ Dear All
Thanks too much. It is so simple. When we learn something from academically, it is not complete. Like "Suppose this is a test tube". Perfection is possible only when we face practically.

Keep up good spirit.


VDS
Click to expand...

@ Dear All,

One more thing, eventhough the result is same, the procedure is slightly different. In first case, apply formula in single cell and stretch upto last. In Second case, copy the entire data and apply custom format.

VDS
 
@VDS

When you are going to use the formula then no need to set the custom format because your result will come directly as you required

Exp
Say your Date range is in A2:A1000 and you want the Date formula to be in "MMMM,YYYY" in the Range B2:B1000
Then you use the Formula as per mine or somendra (Ex. in B2 = TEXT(A2,"MMMM, YYYY") OR IN B2 = Date (Year (B2), Month (B2),1) AND Drag Down up to B1000

or you want Directly change the Date format when you enter any date in A2:A1000 then select the Range A2:A1000 and use Custom format as per my above post explain

Hope it's clear if any problem please inform

Thanks
 
sgmpatnaik said:
@VDS

When you are going to use the formula then no need to set the custom format because your result will come directly as you required

Exp
Say your Date range is in A2:A1000 and you want the Date formula to be in "MMMM,YYYY" in the Range B2:B1000
Then you use the Formula as per mine or somendra (Ex. in B2 = TEXT(A2,"MMMM, YYYY") OR IN B2 = Date (Year (B2), Month (B2),1) AND Drag Down up to B1000

or you want Directly change the Date format when you enter any date in A2:A1000 then select the Range A2:A1000 and use Custom format as per my above post explain

Hope it's clear if any problem please inform

Thanks
Click to expand...


@Dear All

Still I have another doubt, If I make new column, and set the formula, it will display result "month and year" together of a given date. Suppose In that new column, If "December 2013" is repeating more than once, it can be merged. Then after entering new complete record, it will not be sorted datewise and give message "The operation requires the merged cells to be identically sized." Hence, how to sort out this. Please suggest.


VDS
 
@VDS

if possible please give us a sample file with your desire requirement, it is good to solve your problem correctly

Thanks
 
Hi VDS....Yeah it will create problem as when you merge cells, with multiple values all other values get lost and the upper - most left cell value only remains. Now when you apply the filter it will give only one row of data, which pertains to that cell.
If it is like you want a report in that monthly pattern, create a separate sheet and generate Pivot Table for a particular month, with all required data.

Somendra.
 
Somendra Misra said:
Hi VDS....Yeah it will create problem as when you merge cells, with multiple values all other values get lost and the upper - most left cell value only remains. Now when you apply the filter it will give only one row of data, which pertains to that cell.
If it is like you want a report in that monthly pattern, create a separate sheet and generate Pivot Table for a particular month, with all required data.

Somendra.
Click to expand...

@ Somendra

I am new to the pivot table. If time permits, could you pls briefly explain something about it.

VDS
 
Somendra Misra said:
@VDS....
As SGMPATNAIK suggested, kindly upload a file with all data and required output, so that we can guide you accordingly.

Somendra.
Click to expand...

@Dear All
Sorry. I cannot give the data as it contains the vital information of bank guarantees, name of projects, etc.
However, try to upload the sample file shortly.

VDS
 
@VDS

we don't need any personal information here we just want your column heading with dummy dates and dummy contents of your data that's all and your expecting result data

That's all

Hope you understand

Thanks
 
@ Dear All,

I am attaching herewith the screenshot - PNG data because server settings will not allow to do so.

If it is accessible to you, revert soon.

VDS
 

Attachments

  • upload_2013-11-19_16-36-34.png
    upload_2013-11-19_16-36-34.png
    69.6 KB · Views: 13
@Somendra,

Hope, you can see printscreen file.Whenever I am entering the new record and sorting with "Expiry Date" updation becomes difficult. Insert row also is not uniform. Else, it has to be unmerged each and every time after data entry. What I want the data to be sorted with the corresponding field name with the merged column. Could u pls suggest any solutions.

VDS
 
@VDS...

As I told you earlier, once you merge cells only the top value remains and rest all rows became blank for their corresponding rows. For Human eyes it look common for all rows says December, 2013 for 5 rows but for excel December 2013 is for only first row and for rest 4 rows it is blank.

So if you will try auto filter or advanced filter it will return only 1 row. And, when you try to sort data it will not allow.
I would ask you not to merge cell and let the data be remain in every row, until unless your files require that particular type of visuals.

May be somebody more expert in this forum can help you on this.

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