Find Start And End Of Month
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
ADVERTISEMENT
Feb 11, 2009
i have a report that needs to be filled out with total purchases daily that keeps a running total. So each day i have a column with a new figure. Looks like this:
Date Purcahses Total
30/01/09 10 10
31/01/09 10 20
01/02/09 10 30
02/02/09 10 40
etc
On the 20th of each month, i want to create a macro that wipes out the historical data prior to 1st of that month. i.e. on Feb 20, i want to lose all the january rows.
View 2 Replies
View Related
Sep 26, 2007
I have a PM (Preventive Maintenance) spreadsheet which tracks the progress on all our maintenance workorders providing a % of PM's complete each month. It has been manually updated each month, but I want to have Excel remember the percentage for each given month and record it in a seperate cell. I.E. D100 tells me PM compliance is 97% MTD for September, but when October 1 arrives I have to remember to take the number as it was on the last day of the month and record it somewhere else. I want to have excel automatically save the number (%) as it is on the last day of each month and keep it. I have used a simple IF function ( IF(C90>09/01/2007,D100,"") to get the cell to record the number during the active month but I lose it as soon as the next month starts.
Is there any way to have excel recognize the new month then save that number in another cell when the next month begins?
View 3 Replies
View Related
Jun 1, 2014
I'm trying to prepare a Gantt chart and I'm unable to display the start of month date in the x axis. Currently I'm able to display each month by giving an interval of 31 days, but I'm not sure how to display the start of month in the chart. Tried to add a secondary horizontal axis,but the secondary axis is not intersecting with the y axis and the grid is not getting aligned properly.
View 1 Replies
View Related
Dec 25, 2013
I need a formula to calculate a date 6 months forward from the start date. However, in the calculated 6th month, I need it to the day before the start date.
ie: start date: 26/12/13 -- End date: 25/06/14
View 3 Replies
View Related
Feb 18, 2009
is possible to trigger a macro at the start of every month. I have a fairly simple bit of VBA, but just want it to execute on the 1st of every new month.
View 3 Replies
View Related
Feb 3, 2014
I have about 80,000 dates. What I need to do is to place an x by the date if it falls inbetween January. For example, I have start Date in A1 and End date in B1. If the start date was 10/20/2013 and the end date was 6/11/2014 then that woub give me an "x" because it went through January. If the start date was 11/23/2013 and the end date was 1/1/2014 that would give me an "x" because it fell in January but if I had 8/4/2013 as a start date and 12/29/2013 then that would be blank. Hope that I made myself clear enough.
View 2 Replies
View Related
Jun 3, 2014
I am working on a formula by it is repeatedly failing.
Based on input 1 which is the month name, I am looking for a formula to identify the position across columns as the starting cell and then the value in input 2 needs to be repeated 12 times from there.
Example:
Input 1 is Apr-14
Input 2 is 51.8
I have columns with month names starting Apr-13 to Mar-15
Since the input 1 is Apr-14, the formula should identify the appropriate column named as Apr-14 and from there it should copy the value (input 2 ) 51.8 for the next 12 cells which is until Mar-15. I have attaches the examples for your quick reference.
View 3 Replies
View Related
Mar 26, 2014
I've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.
FTE Calculator non nursing.xlsx
I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.
View 2 Replies
View Related
May 23, 2007
I have data as shown in the attached image file. Though the example image shows that the data starts from cell D8 but it could start from J30 or any other cell for that matter.
View 9 Replies
View Related
Jul 11, 2007
I have a list of employees and the dates on which they were absence from work for a set period (i.e. one month). Some employees have been off for a day, some for longer. This data has been pulled from a large datasheet and now needs formatting to add in the start and end dates of absence, ready to upload to a payroll system.
The Problem:
I have been able to get a macro working on a simple list of names, however the problem arrises where I have 2 employees off on the same day - I was using a loop to find the date last used, but this no longer works.
My Request:
A copy of the worksheet in question can be found here : http://www.carpe-luna.com/other/AbsenceQueryHelp.xls
But I'll try and describe the layout as much as possible
This is how my raw data is set up. (Up to row 50 but potentially more)
A.........B...........C..............D...........E.........F
EmpNo.....EmpName.....Start Date.....End Date....NoDays....NoHours
612.......Paul........26/01/07.............................12......
612.......Paul........27/01/07.............................12......
615.......Ian.........27/01/07.............................12......
758.......Peter.......16/01/07.............................12......
758.......Peter.......17/01/07.............................12......
758.......Peter.......18/01/07.............................12......
...........................................
View 9 Replies
View Related
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
Nov 7, 2002
In col A I have various text codes in no particular order: i.e.cell A2 is PM-A01, cell A3 is BTC05, cell A4 is PM-B00, etc. The first two positions are always alphabetic. I want to sum all the numbers in column B whose adjacent column A text starts with "PM".
I tried =IF(match("PM*",A2:A100,0),b2,"") but just get "NA"
View 9 Replies
View Related
Mar 14, 2014
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
View 3 Replies
View Related
May 16, 2014
Is there a way to force Excel to look at only the first characters in a field when searching?
If I use:
MySearch = Range("C3").Value
Cells.Find(What:=MySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
It will find any instance of the characters entered in C3; however, I want it to find only fields that START with those characters. E.g. if I enter TRA in the search box it will come up with CITRATE when I want TRACLEER, etc.
View 7 Replies
View Related
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
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
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
Jul 30, 2007
This is a sub that uses the Find method to find a series of dates and copy them to another worksheet. The following error comes up: Object variable or With block variable not set. I have tried using a set command etc. but other errors end up coming up.
Private Sub CommandButton7_Click()
On Error Goto errorHandler
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (mm/dd/yy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)")
If stopDate = "" Then End
'startDate = Format(startDate, "mm/??/yy")
'stopDate = Format(stopDate, "mm/??/yy")
startRow = Me.Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Me.Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Me. Range("A" & startRow & ":A" & stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
End Sub
View 9 Replies
View Related
