I need to extract the month name from a cell. E.g In one cell I have a time stamp, which comes up in this formart.
Cell A1 is "5/30/2007 10:03:37 AM". Basically i want cell B2 to say "May". Please note this format is using the american date format where the order is month/date/year.
Is there a way to do this? Remember one cannot look at just the first digit as what happens for months 10 & above? At the moment I do this manually.
My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.
How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.
I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.
If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.
I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?
We use a bunch of equipment at work that spits out a pdf file that is going to consist the results of a particular test.
Those files are named in a unique format. It looks a bit like this:-
EH_CM42_EB0C8105G00_CPS41D_F30C3105E00_2013_09_10_08_39_25.pdf
Out of this file name it consist of the serial number (F30C3105E00) of the equipment which is the first set off italics/bold and time(08_39) and date (2013_09_10) this test was performed.
So My question is there a way or a vbs macro or anything that will be extract these three different information and save them in three different cells on the spreadsheet. BTW Also I should mention that these files are going to be stored on a network drive
PS- The only way I have figured out to do this is to create a .bat file that runs the dir command and saves it to a text file.
I'm getting people to set up the dates as mmm-yy which makes it so much easier to run a macro with dates. However I still need to search on d/mm/yy (e.g. 1/04/2012 for April). Is there a way of allowing the input box to have April 2012 match 1/04/2012?
I've currently got a message in the Input Box specifying that people need to type in the specific first day of the relevant month.
I have the Month and Year in the name of the workbook for eg Sales July 2013.xlsm
I would like a macro to extraxt the month and year from the file name and put this in shewet2 in cell E1
As above, how do i retrieve excel file names in a specific folder and place the names into a specific column?
Say there are 2 files named UAT1.xls and UAT2.xls and i have a master file to put all this data in.
I have a column containing names, blank spaces, numbers, a bunch of miscellaneous things. However all I want to do is extract only names, into another sheet, and not the numbers, blank spaces, etc. Youi'll see what I mean when you open the attachment. Offset isn't an option because the names don't appear in the column in an organized fashion.
Dummy workbook.xlsx
I would appreciate some pointers on how to write a function(s) or macro to do the following in Office 2003:
- I get a weekly report (from Quickbooks) in Excel format which shows inventory usage.
Showing the relevant sections:
Col D Name
Col O Qty
Col Q PxQ
The report also throws out Total is Col D, but only for Col Q, not Col O (which is needed)
So, R5 thru 7 may show Apples
And R 11 shows Total Apples
Then R13-14 may show Oranges
And R15 shows Total Oranges.
The report has about 400 lines currently, and about 50 items......
I have this macro (below) which produces the the date on a file name when I update it every month. However, I would like it to show a different version of the date and in a different location.
At present it produces the following (example):
Ice_Cream_Report_Month_07-02
What I would like is:
Ice_Cream_Report_Month_Feb07 (or even Feb-07 would be fine)
Macro:
Option Explicit
Private Sub ComboBox2_Change()
End Sub
Private Sub ComboBox3_Change()
End Sub
I will preface with what I am trying to do. I want to populate a combo box with months. The list will be populated based on a the date range in a dataset, so all 12 months may not bee populated in the combo box. I want the months to be listed in the combo box with their text names displayed(Jan, Feb... Dec) but have their numerical values when selected (1,2... 12). I'm sure there is a way to do this, but for the life of me I can't find how. My initial thought was to assign a value to a specific list index, i.e. assign the value of listindex 6 as "Jun", but I can't figure out the syntax to do that.
This doesn't work, but might give some insight into what I am trying to do
form.combobox.ListIndex(6) = "Jun"
I've been trying to solve this problem all afternoon and evening but cannot think how to do it. I'm a basic (very) VBA user and the best I could come up with is below.
I have a sheet called Data. In column A I have names, and in column B I have dates (day, month and year).
What I am trying to do is to filter the unique names that occur during the selected month and year (day is irrelevant) and then count the number of times that name appears in the selected time period.
This is what I have so far, but it's not working at all!
Sub countNamesMonth()
Dim rangeEnd As Long
Dim i, y, x
Dim cell As Range
rangeEnd = Cells(Sheets("Data").Rows.Count, "A").End(xlUp).Row
y = 2
x = 2
In the workbook the sheet names are month names e.g. july,august,September etc and 1st of each month is to be entered in a cell in each the sheets. By some trial and error I wrote this macro
VB:
Sub test()
Dim j As Integer, monthnr As Long, monthname As String
For j = 1 To Worksheets.Count
[Code]....
This worked in July sheet it is written as 7/1/2013 and in august sheet it is 8/1/2013 etc Then I shortened the macro like thlis
VB:
Sub test()
Dim dte As Date
dte = "july" & "/1/2013"
Range("A1") = dte
End Sub
It worked. Even if I use shortened 3 letter form of month like "Jul" or "aug" then also it works
But if type on the spreadsheet itself
="july"/1/2013
It Gives value error.
Perhaps it works only in vba and not spreadsheet.
I accept nobody is going to use "july" & "/1/2013" instead of 7/1/2013 But this will be useful if sheet names are month names so that when we write the date we can use sheet names Mine excel 2007 and windows 7.
I'm trying to achieve is to write a macro that can search a column of dates then open new worksheets according to the months that are present in the column of dates. So, for example, if the column has dates ranging from January to June, I need the macro to open 6 new worksheets and label them January, February, March, April, May and June.
View 6 Replies View RelatedI m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies View RelatedCan I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies View RelatedCode:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
I am using Excel 2003 and Windows XP.
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View RelatedI have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.
So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...
So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.
Attached File : Time and Attendance.xlsx‎
I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.
I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.
VB:
Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")
Do While Not IsEmpty(Cell)
If Cell = "Date" Then
[Code] .....
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies View RelatedIn a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536