Transferring Portion Of Date From One Excel To Another?

May 28, 2014

Transferring a portion of a date from one excel to another. On the file attached, the 1st column lists the date, and the second column needs to show the date in text format.

3-Jan-14 —> “January”

How this can be done?

Attached File : Month Problem.xlsx‎

View 4 Replies


Retrurn Date Portion Of File/Workbook Name To Cell

Sep 27, 2006

I am using Windows XP and Excel 2003. The filename I want to open is called "MMO Activity Report 25-09-06.xls". Part of the filename as "MMO Activity Report" will never change but "dd-mm-yy" will change constantly. I want it to show in active worksheet cell A1 in format YYYY.MM.DD ie. 2006.09.25. I type in Sheet1, cell A1 as "=filedate()" but it shows me "#VALUE!"

Function filedate()
Filename = Mid(Right( ActiveWorkbook.Name, 21), 1, 9)
filedate = Format(DateValue(Mid(Filename, 1, 2) & "-1-" & Mid(Filename, 4, 2) & "-1-" & Mid(Filename, 7, 2)), "")
End Function

View 6 Replies View Related

Transferring Date Form Userform Textbox To Cell In Worksheet?

Jan 26, 2014

I have a userform with numerous text boxes. One textbox is used to input a date with the following code

[Code] .....

When I have completed my userform I want to export this date as a date into range(a2) so I can then use the filter function to filter by month or year. THe problem I am having at the mo is that in the userform it is formatted as dd mm yy, but when I export it from userform it changes to mm,dd,yy. So if I entered 12th January 2014 it changes it to 1st December 2014.

A workaround is to export as text but then I use the filter by month year function so would prefer to avoid this.

View 1 Replies View Related

Excel 2010 :: Transferring Array Back To Worksheet?

Jun 20, 2014

I have a workbook that is doing something I don't understand. In several places I am using arrays to transfer/modify information, then placing the array back onto the worksheet, and this works perfectly. The following code... doesn't.

(I've cut out unnecessary code like variable declarations - all my integer items are declared as Long)

Dim varA As Variant, varB(10000, 1) As Variant
Set wkDest = Sheets("a")
erB = 0
For lpTerm = 1 To 4


and several other ways. What happens is just null output, a full column of nothing. However, when I expand my worksheet range to two columns, all the data from varB appears - it is shifted down one column and row. So my data starts at .cells(2,2) instead of .cells(1,1). The data is all perfect, I've verified it left and right.... I just can't figure out why it's getting shifted like this.

View 2 Replies View Related

Excel 2007 :: Transferring Columns With Same Titles To Another Sheet Automatically?

Aug 18, 2014

Excel (2007) user however I'm not very well versed with VBAs and Macros.

I have three sheets and on each sheet the first 5 columns (A - E) have the same title (all in row 1, frozen top row and with filters) there are other columns but I only need A -E to auto transfer.

Sheet 1 is named "Total"

Sheet 2 is named "Management Referral"

Sheet 3 is named "Health Assessments"

What I'd like is for any entry that goes into any row in both sheet 2 and 3 to automatically transfer to sheet 1, but only the information from columns A - E.

There will be dates (dd/mm/yyyy) in column A in each sheet and when transferred to sheet 1 they must be listed chronologically oldest to newest.

I also need all the information in columns A - E that transfer to sheet 1 to stay in their respective sheets.

View 3 Replies View Related

Excel 2007 :: Cutting Portion Of Cell Value To Cell Comments

Aug 9, 2012

I have an Excel 2007 workbook with several sheets, and various cells in several of the sheets have column headers with a description in the same cell enclosed in curly brackets, e.g. Header{Description}. I would like to to keep the column headers in their respective cells, but move (cut) the descriptions along with the brackets to the respective cell comments.

View 1 Replies View Related

Excel 2007 :: Date Filtering - Change Date Format Supplied By Date Picker

Apr 24, 2012

I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.

I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).

When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)

So my questions are:

1. Is this due to the formatting?

2. Is there a way to change the date format supplied by the date picker?

View 7 Replies View Related

Hyperlinking Files With A Portion Of Name

Apr 15, 2014

I have a few thousand files that I'm trying to hyperlink into excel. The files are named with a date at the end of it. I want to be able to hyperlink to that file just by looking up the name without the date portion. So basically I'm asking if I could hyperlink a file with only a part of the name, and yes the names are distinct even without the date. I now am loading all of the file names into a spread sheet via a VBA code, however with almost 10k files that process takes longer than I'd like.

View 7 Replies View Related

Sum Portion Of Array Without Loop

Jun 24, 2009

I faced a problem to attach this small WB as an XLS

I'm looking for some way to some the red cells without looping.

In this example the array was filled with A1:A10 values.
In the real situation the array gets its values from other source than a Worksheet Range.

As you can see I manged to transport the Array Values to F1:F10 and from here I could calculate the sum of F3:F8 but I do not want to use any helper columns.

View 12 Replies View Related

Sorting From Portion Of Cell

Feb 26, 2009

I'm trying to sort a column of email addressees to determine how many have the extension .ac - basically I need to know how many student addresses are present in the column.

View 9 Replies View Related

Protect Portion Of Sheet

Oct 13, 2009

I find the protection options of Excel confusing. I have a pivot table. Alonside it our a few formula columns. I want to protect only the columns outside the pivot but can not get it to work. I tried this:

1) Highlight entire sheet
2) Format Cells Protection - remove checkmark from: Lock Cells
3) Highlight 4 rows outside pivot
4) Format Cells Protection - place checkmark in Lock Cells
5) Tools Protection Sheet - supply password

The result is that the 4 columns outside the pivot are indeed locked BUT SO IS THE you can not utlize the dropdowns!

It must have something to do with the pivot, on a regular sheet (no pivot) it works fine. Further if, while Protecting the sheet, I click the: allow Pivot table reporting box, then it allows dropdown usage but blows up as soon as you select something with an error about not being able to redisplay selected itemsbecause of protection being on....

View 9 Replies View Related

Lookup For A Portion Of A Text Value

Dec 16, 2009

I want to lookup a text value in an array (vlookup). However, instead of searching for the exact value I want to search for only the exact match of the first four characters of the text value. Example

If I have in Cell A1 the text value: 5154
In cell B1 I want to lookup 5154 in the values cells C1 to C6

The result would be 515476

I tried with this:


View 9 Replies View Related

Extract Portion Of Address

Jan 5, 2007

I have read Dave Hawley's reply to a member using LEFT and MID but I can't seem to make what I want, to work.

I have an address in the follwing format:


I need to extract the '12' to be placed in one cell, and the 'MAICH ROAD' in another cell.

Unfortunately not every address has a street number and is sometimes listed as follows:


I still need to be able to put the 'LEYTON WAY' portion into the same column as the 'MAICH ROAD' in the previous example.

View 3 Replies View Related

Return Decimal Portion Of Number?

Mar 22, 2008

how do i get the numbers after the point.

View 9 Replies View Related

Possible To Ignore Text Portion Of Given Cell

Jul 26, 2013

I was wondering whether it is possible to have excel ignore the text portion of a given cell. Or alternatively, have excel add in units (or specified text) after you input an initial value so that the cell can be used in a formula.

I have noticed that if I have cell A1 as 1 min and autofill, excel will recognize the number value and still put 2 min in A2, 3 min in A3, etc. However I can not use a function or any formula as the result will be #Value or another error.

I am looking for a way to be able to use the cells in formulas, and I do not want to put the text portion of the cells into another row/column.

View 3 Replies View Related

Save Portion Of Worksheet In PDF And Email Out

Jun 26, 2014

I have the following code in VB from somewhere. How to modify or adding from the following codes.

(1) Is anyway I can modify sedning out PDF instead of the actual workbook?
(2) Is anyway I just want the range from (A1:G80) instead of the whole sheet for printing (PDF).

View 3 Replies View Related

Copy Portion Of Filtered List

Jul 7, 2009

I need the VB code to copy just a portion of a filtered list. I have completed the code to sort and filter the list. I'm having a problem determining how to define the region needed. I have searched the forums and found a few helpful threads but nothing specific. Most show selecting all the columns of the filtered list.

My list is in columns A:AA and begins in row 4 (header row). In my test data, there are 5,900+ records and filtered list is approximately 4,900 records. Since I have sorted the data, the portion of the filtered data I need will always begin in cell D5, be columns D:K, and be the visible rows.

View 4 Replies View Related

Sort By Portion Of Data In Column?

Mar 19, 2012

Is it possible to sort by column, but only a specific portion of the data within the field?

For example, I have a set of numbers, 5026125120. I want to sort my spreadsheet by the 3rd and 4th digit only of that set of numbers (5026125120).

View 1 Replies View Related

Formula To Take Number And Add One To The Portion Before Hyphen

Mar 22, 2012

I am in need of a formula. I have a number like: 23847-2

I want a formula to take this number and add one to the number before the hyphen. I also want the single digit number after the hyphen to be able to be changed.

So in the end: i want the number copied to the next cell, add one to the portion before the hyphen. Then allow the number after the hyphen to be changed manually.

View 7 Replies View Related

Leave Portion Of Text Formula

Jan 27, 2014

C4 - #01941 (ATF Cleaner)

I have asked this before as to a formula to get rid of the text and only leave the #01941. The formula I received is =LEFT(C4,FIND(" ",C4)-1) and it works fine.

My queston now is there a way to only leave ATF Cleaner? No ( ) around it?

View 4 Replies View Related

Extracting Portion Of Text From Cell

Nov 16, 2006

I have made something similar before and we were able to make it work... I tried the same code, but it is not working now. Basically in cell a1 I have

Auction Type: Auction, Auction End Time: 11/15/2006 10:00:00 AM(MST), Asking Price/Current Bid: $10.00, Number of Bids: 0, Description: , Traffic: 28
and in cell b1 I need it to only display $10.00 in cell c1 I need 0 (for number of bids)

View 9 Replies View Related

Extract A Portion Of A Text String

Jan 1, 2007

I have a text strings that contains the word "CHQ 123456" or some other CHQ number. There might be another word/words after the word CHQ xxxxxx something like:

Payment by CHQ 123456 against your inv 45225
ABCD Company Limited - CHQ 187546
PO 4520061257/CHQ 745865/invoice Number 4125

I need a UDF that extracts the CHQ No. from the text string

CHQ 123456
CHQ 187546
CHQ 745865

View 9 Replies View Related

Formatting A Portion Of Formula Results

Aug 28, 2007

I have the following formula (basically a concatenation of a few different cells):

=R2&", "&Q2&" {"&U2&"}"&" ("&V2&" Days)"
I was hoping to make bold and maybe larger font the " ("&V2&" Days)"
portion of it...
is that possible?

View 9 Replies View Related

Extract Portion Of String Using Formulae

Apr 11, 2009

If I have a string of the form:


I would like to extract the ABC term, i.e. the term that liues in between the 2nd and 3rd "_" in the Workbook name (in the above example "Test_ABC_2009_v2.xls")

View 9 Replies View Related

Return Decimal Portion Of Number

Mar 22, 2008

trunc gives just the numbers before the decimal point. how do i get the numbers after the point. original number minus trunc seems messy!

View 3 Replies View Related

Highlight Portion Of Text Of TextBox

Mar 27, 2008

The following code highlights part of the time (hours or minutes) that will be modified by a spin button. The code works fine (although maybe a more efficient way). Each time you click in the time it will highlight the hours or the minutes, depending on where you click. When you click on the spin button the control loses the focus so becomes un-highlighted. I thought that just setting the focus back to the text box would work but what I am getting now is it highlights on every other click of the spin button.

Private Sub spnTime_Change()
Dim dtTime As Date
Dim y As Integer
If strTimeChange = "" Then
MsgBox "Please click on a time to modify it"
Exit Sub
End If
dtTime = Format(ctlText, "hh:mm")
y = Me.spnTime.Value...............

View 2 Replies View Related

Macro To Copy The Portion Of Text In Cell?

Jun 2, 2014

write a macro to copy a set of sentences in an Excel Cell.

Excel cell contains set of sentences under 2 categories.

OK Points:
* ----------
* ----------
* ----------

Improvement Points:
* ----------
* ----------
* ----------

I need to copy the sentences under the heading improvement points. Enclosed trial excel sheet for further ref.

View 1 Replies View Related

VBA Code To Change Portion Of Array Formula

Jan 11, 2009

I have been exhausted with a workbook I have been working on for quite some time now, and hope I can get some help here.

I have the following array formula in Sheet1, S2 and copied down to S10051:

View 14 Replies View Related

Percentile Applied To Portion Of Group Of Numbers

Nov 29, 2011

I have a list of 400 numbers. I wish to filter down the list to those 500 and then of that list get the 95 percentile.

I have tried

=IF('GRML ECP 01'!O$2:O$584 500 I get a blank. When I run the function manually on the numbers > 500 I get 1800.

View 3 Replies View Related

Using INDEX With Dynamic Workbook Name - How To Format Name Portion

Jun 29, 2013

I have a range of workbooks that I need to access when closed. I therefore want to use INDEX rather than INDIRECT.

This non-dynamic INDEX reference works perfectly:

=INDEX('[AAA 100.xlsx]Sheet 1'!$A$1:$F$20;1;1)

In the future, however, there will be a lot of reference files and both portions of the name will change - I might have ABC 123, DEF 345, etc. These name portions will be stored in cells. Cell A19 might contain "AAA" and Cell A20 might contain "100".

I am trying in my ignorance to get something like this to work:

=INDEX("'["&A19&" "&A20&".xlsx]Sheet 1'!$A$1:$F$20";1;1)

But I just get "#VALUE!".

Stepping through the evaluation, it seems the final value to be evaluated is this:

INDEX("'[AAA 100.xlsx]Sheet 1'!$A$1:$F$20";1;1)

The outermost double quotes therefore seem to be causing the problem, but if I remove them I get "#REF!".

View 4 Replies View Related

Copyrights 2005-15, All rights reserved