How Can I Find My 1st Of The Month
Mar 26, 2009
am working on a time sheet project. i'm now working on the monthly holiday planner.
Am looking for a way for a formlua to look at 2 rows and return the first of the month i.e 01-01-2009
The rows which need to be looked at are the first two rows of any given month because the 1st of the month will always be in the first two weeks of a month.
my rows are
1st week to look in. b4-h5
2nd week to look in. b19-h19
Now b4 as a paste link in it ..=roster!$H$2 which is the start date of the roster
the result of the search is then put into b2 as Month only ie. January
View 9 Replies
ADVERTISEMENT
Dec 17, 2009
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies
View Related
Dec 23, 2008
I 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 ...
View 4 Replies
View Related
Jan 28, 2013
I am looking for a smart way to extract the values corresponding to the first and last day of a month I specify from my time series. Column B has the dates (dd/mm/yy), column A is text(B,("mmm-yy") and column E has the values I need to extract. I have a column with all the months of the year: Dec 12, Jan 13, Feb 13 etc... and I need to pick the value in E that corresponds to the last observable day of each month and divide that by the value corresponding to the first observable day of the month.
[URL]
View 5 Replies
View Related
Apr 9, 2014
I need a macro that allows a user to input a Month and a Year on an input box. When this is done the macro would pull out the date for each Monday (in the format DD-MM-YY) in that month and paste it into four or five cells (A1:A5)
Eg for April 2014
A1
A2
A3
A4
A5
07-04-14
14-04-14
21-04-14
28-04-14
View 2 Replies
View Related
Apr 16, 2013
I want to put "Jan" (as in January) in a cell on Sheet1 and in cell A1. In cell A2 I want to dispay the start of the month and in cell A3 the end. So if I change A1 to "Feb" or any other month it will display the correct start and end dates.
I then want to use either the month or the dates to do a SUMIF statement. It a simple one like -
=IF(C5="","",SUMIF(April!R:R,"="&C5,April!G:G))
But I don't want April in the formula as I just want to update the cell in A1 to read in the data for a different month. BTW, April is on another sheet along with a sheet for each month.
View 9 Replies
View Related
May 4, 2014
I have excel data like from b3 to b35 there are different dates with different months and years in long date format and from G3 to G35 expenses in accounting format. what i need is the ONLY last month expense in another worksheet.
View 1 Replies
View Related
Oct 16, 2009
I have two columns: 1) Last Date of each month from 2005 and 2) Value.
Date format: dd.mm.yy
For example:
31.01.05 1.7
28.02.05 -0.7
31.03.05 2.6
30.04.05 0.4
....
How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.
View 3 Replies
View Related
Dec 29, 2009
Please tell me how to find the birthdays in the current month.I m uploading the worksheet for reference.I want to know the formula in this context.
View 9 Replies
View Related
Jan 2, 2013
I've been using the following code to go the current date on a spreadsheet. I now have to add the current date somewhere else on the sheet How do I modify the following code to only have it look in A:A ? Date is formatted -- m/d/yyyy (1/2/2013)
HTML Code:
Sub gotodateday()
Dim C As Range
Set C = Cells.Find(Date)
If Not C Is Nothing Then C.Select
End Sub
If I name ranges as Months, (Example: January_13. -- I can use the month without the year if it makes a difference),. How would I change it to look for the current month in A:A.
View 6 Replies
View Related
Jun 12, 2014
I'm working on more code to take data from hotel reports, compile totals and transfer data to several other workbooks. In prior instances, I'd been working with data that had been converted from Adobe .pdf format, so while the cell values appeared to be dates, they actually were text. I'd come up with a solution to find the first of each month in a column of dates and insert a row at that point. However, I'm now working on this with a system that actually outputs in Excel format. As such, the date column is actually normal date serial value, rather than text and my code doesn't work. What can I do to modify this snippet of code to find the first of each month and insert a row when the values in the column are actual date serials?
Here's the current code:
With Sheets("Sheet1")
For RowToInsert = .UsedRange.Rows.Count To 14 Step -1
If (Range("A" & RowToInsert) Like "*/01/*") Then Rows(RowToInsert).EntireRow.Insert
If (Range("A" & RowToInsert) Like TodayVisualMatrix) Then
Rows(RowToInsert).EntireRow.Insert
Range("A" & RowToInsert).Value = "Subtotal"
End If
Next RowToInsert
End With
Row 3 doesn't find anything because while they appear in that format, the value of the cell isn't */01/*....
View 6 Replies
View Related
May 27, 2009
i have a table with a row of dates along the top. beneath each date is a value.
the dates and values in these cells changes based on info in other sheets.
i need a way of finding the first date in the current month and returning the corresponding value.
View 9 Replies
View Related
Jun 18, 2014
I have 4 grids on the trending tab. I want to find the top 10 highest Color assets from Fleet Volume -Color tab under the correct month.
So for example, if the month is May as in cell G3 on the trending tab, then look at the data under the month of May on the Fleet Volume-Color tab and find the top 10 assets and drop in the City, Address, Model, Serial Number and then volume to the 1st grid on the trending tab, then repeat for Highest B&W
I want to the do the same for each of the other 3 grids on this tab. I want this to update based on the month on both tabs.
Of course, the data on the Fleet Volume tabs is a small range due to size, the data is a lot larger.
Is there are way to do this with a formula? I tried Large and small formula but not too sure how to bring over the other data like City, Model etc.
View 4 Replies
View Related
Apr 22, 2009
In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)
The Week ( Monday to sunday) which need to be calculated is the week no. in the given month
like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april
View 14 Replies
View Related
Jan 30, 2010
Can Anybody help me with a function that will arange all days from January acorrdingly with the year 2010. I am attacing a sample file to make it clear. I need the function to Check which month is in Cell H1 and arrange the days accordingly to the month and the year.
In my example the code must change the Cell values Like this: ...
View 9 Replies
View Related
Dec 13, 2006
I've read several threads about using Median(If, and I have success with one criteria, but not with three. Here's the setup:
I have a log of phone calls, and I want to find the median call length for calls in a certain month or set of months (i.e. a quarter, three months). In Calls!B I have the date of the call, in Calls!C I have the call length in minutes (i.e. "34"), and in Calls!A I'm using Concatenate and Text to return month/year (i.e. "Aug06").
With one criteria, it calculates the median correctly. With three, it only returns #NUM. Yes, I hold control+shift and hit enter. Here's my formula:
=MEDIAN(IF(Calls!$A$2:$A$500=Scores!B8,IF(Calls!$A$2:$A$500=Scores!C8,IF(Calls!$A$2:$A$500=Scores!D8,Calls!$C$2:$C$500)))))
Where Scores!B8,C8,D8 hold "Oct06" "Nov06" and "Dec06" respectively. What am I doing wrong? Is there another approach I should use instead?
View 9 Replies
View Related
Jan 28, 2010
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.
View 9 Replies
View Related
Dec 25, 2013
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 Related
Jul 10, 2014
I 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‎
View 2 Replies
View Related
Apr 4, 2013
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] .....
View 5 Replies
View Related
Feb 13, 2010
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 Related
Dec 1, 2012
In 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.)?
View 3 Replies
View Related
May 1, 2006
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.
View 11 Replies
View Related
Mar 20, 2009
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
View 9 Replies
View Related
Jul 1, 2009
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
View 9 Replies
View Related
Jan 21, 2010
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.
Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
View 9 Replies
View Related
Jul 19, 2006
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 Replies
View Related
Feb 16, 2012
I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
View 14 Replies
View Related
Feb 4, 2009
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month.
Goal:
I need to format the spreadsheet to make it more readable and have been assigned the task of:
1 - Inserting a blank row between each row that contains data and filling in with color.
2 - Resizing the blank row to make it look like a "thick" border.
3 - Auto adjusting the columns to correct size.
4 - The last column contains comments and needs to be wrapped text.
5 - All of this needs to fit on 1 sheet (landscape).
Issues:
1 - Each month there will be a different number of rows.
2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls?
I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.
View 2 Replies
View Related
Oct 24, 2011
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?
View 6 Replies
View Related
