Check Many Weeks In A Month
Jan 19, 2008I need a formula that will tell me how many weeks there are in a month.
If cell A2 had the month and cell B2 had the year then I would like to return 4.....
ADVERTISEMENT
Summing By Month From Weeks
Jan 23, 2007I have a weekly forecast for what will be sold for the upcoming year and want to sum the quantity by months. For example, 1/6/07 sell 351 units, 1/13/07 sell 315, 1/20/07 sell 1,165, and 1/27/07 sell 328 units so Jan would return 2,159. To avoid future user error, I'd like to sum by month without the basic sum( range) formula and have tried the following SUMIF statement:
=SUMIF(Weeks,"=Jan*",Widget_Qty)
That returned zero. I formated my Weeks to look like "6-Jan-07" so I thought that the month name could be used as a text but this was also wrong. I also tried changing the month value from text, i.e. Jan to a date value displayed as a "mmm" but failed again.
Displaying Weeks In Selected Month?
Feb 18, 2014I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
Data Conversion From Month To Weeks
Nov 16, 2006I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on.
I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones.
So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)
Create Subgroups For The Weeks Within The Month Groups
Jan 8, 2009I have a spreadsheet with many workbooks. Each workbook contains sale figures for all products for each product range over a period of time. There is one workbook that totals the figures from other workbooks.
This spreadsheet is updated by another department on a weekly basis with new figures added to each workbook (products within each product range are also likely to increase).
Currently, the data in each work book is grouped by months (with a row summarising the month's figures). I can create graphs to visualise monthly performance, but also need to have a weekly performance graph/chart. Thus, the data also needs to be grouped by weeks.
I have tried creating subgroups for the weeks within the month groups, but it seems impossible (?!) to do when a week subgroup comes right in the middle of a month summary (e.g. Dec 2008 Summary comes in between Week 29 Dec 2008 - 04 Jan 2009). I have attached an example spreadsheet in case my description was too convoluted!
Flowing Month Data Into A Chart Based On Weeks?
Feb 27, 2009I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.
i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.
What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
If Statements: Option Of Between 3 Weeks And 6 Weeks
Sep 7, 2009i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
Check For Month Change?
May 8, 2013I have column with dates in it. What I need to do is check if the cell below changes to month beginning then generate 1 else its 0.
See example: Cell A2 is same month as cell A1, hence 0. But Cell A4 changes to a new month hence should be 1. So basically I have to check two dates but within this set criteria. Last few days of the month to first few days of the month.
A1
28/04/2013
A2
29/04/2013
[Code].....
Check Month & Year Of Dates
Aug 20, 2007I am trying to calculate the number of days it takes to complete a project when the project ends in a specific month. If it is July 2007 I want the number of days for each project completed in July 2007. The number of days is not the problem it is reflecting the number when a project takes longer than one month to complete. To add to things the users sometimes have "open" or "00/00/00" in E (for never close).
I have a spreadsheet with dates a project begins in column D and dates the project ends in column E and a start and end date for each month going across rows 1 and 2. When the project starts and ends in the same month life is good and my original formal of greater than and less than the dates (courtesy of this website) is effective. I tried the following formula in F34. Each piece of the formula worked but when I combined it I either broke it or have too many nested formulas.
=IF(OR($E34="OPEN",$E34="00/00/00",$E34>F$2),"",IF(AND(MONTH($D34)=MONTH($E34),IF($D34>F$1,$E34<F$2)),$A34,IF(AND(MONTH($D34)>MONTH($E34),if(MONTH($E34)=(MONTH(F$2)-1)),$A34,""))))
I created a work around by putting formulas in B and C and row 3 that would answer some of the nested if and working off the results. I was hoping someone might be able to help me make this a little more effecient.
VBA Code To Check Month And Give New Product
Jan 14, 2008I hv following data in the excel.
I hope some one can help me to create code to do the following :-
1) if have last purchase date , but no last issue date = New Product.
2) if no purchase date , also no last issue date = None stock issue.
3) if have last purchase date , also have last issue date = cal the date between and put the month value .
For example :-
if last purchase date = Oct 07
if last monment date = Nov 07
then the stock duration = 1
VBA Code To Check If Service Required For A Machine In That Month And Create Popup
May 16, 2014What I'm basically trying to do is make a code that looks at where the "x" values are in my range and if the month matches the current month, open a pop up that says "Service Required: Equipment Name" when the file is opened.
If it's any easier, an actual date could be written instead of an x.
An alert system for service required if you will. I have attached a file.
TestSchedule.xlsx‎
Open Multiple Files, Check Current Month & Save To New Location
Sep 15, 2007I have 40 files in one folder which I named it as "CA" + month's name that I am working on. I need to do analyse these files monthly and save it under new folder. how do I automatically save them in new folder and name them for that particular month. Also, each file has worksheet which has one cell as "Aug-07" and the cell next to it has number of that month that is "08". How do I automatically change this also based on the name of the file, because file name month and month in the cell are the same.
View 9 Replies View RelatedMacro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell
May 28, 2008I am trying to allow the Command Button when clicked to go through multiple conditions before making a decision. So, when someone clicks on Command Button 3 the code should look to see if CheckBox1 is true, then it should check today's date, and if it is between a range of days, or even months, then it would add the number in TextBox1 with the amount already in cell H18. This event will happen every time someone clicks on the Command Button.
The end result is to have several sheets (4 total) for each quarter in the fiscal year, and if the dates are within those parameters, the clicking of the command button will update the correct sheet.
Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
Dec 23, 2008I 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 ...
Check Whether Date Is A Month After Previous Date?
Dec 12, 2013I'm sent a file every day where column A contains a month & year but in "general" format.
For example;
Column A, starting with cell A1 with the next date in A2, etc
Jan15
Feb15
Mar15
Apr15
Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.
Averaging Of N Weeks
Apr 28, 2007I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....
View 9 Replies View RelatedRolling Totals For Several Weeks
Aug 27, 2008I have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.
I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).
Is it possible to do this?
I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.
Converting Dates To Weeks
Jun 27, 2013Is there a way to convert data that has dates to week by week? such was week 1 week 2 week 3 etc....
View 8 Replies View RelatedDelete Workbook After Two Weeks
May 17, 2009Is it possible for a workbook to delete itself after two weeks or by date since putting in VBA codes?
Or does it have to be active to be able to delete itself.
I'll be going away for a holiday in two weeks (yepeee) and the person who's taking over my place will only use it on first day of my absent.
I do not want others to use my file (of course I'll back it up before I'll go).
Converting Dates To Weeks
Jul 20, 2006I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
Automatically Count Weeks
Jul 26, 2008Here´s the deal: based on weekly reports, I need to count and sum orders created in our CRM.
I´m exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:
1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD
I don´t need help with the last one, that´s the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.
So, my questions are...
1. I need to set different formulas for count and sum, that´s clear, but how can I make this autimatically without having to change the formula each week?
2. Do I need to consider the date my main file is modified, and count backwards?
As usual I am not sure I´m being clear, though I hope I´m getting better at this. I´m attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.
The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.
Calculate Time Over Last 2 Weeks?
May 15, 2012I have a calendar. All months across the top, formatted as date mmm with content 1/2 2/1 3/1 4/1, etc. so the cells display Jan Feb Mar Apr, etc. All days down the side formatted as number displaying 1, 2, 3, 4, etc. All the days of the year are formatted as time h:mm.
I want to create a cell that shows me the total time for the last 14 days.
Macro: How Work With Weeks
Oct 22, 2008I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)
A second thought was to copy paste them to a new sheet for each seperated week.
and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week
i thought maybe working with boxname?
Turning Dates Into Weeks
Jan 14, 2009"Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).
I need to turn that date into the week that it falls into within a particular quarter of a year.
1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).
And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).
Function - Stock Weeks
Aug 7, 2009I have limited experience at writing functions and I can't seem to get this one to work, in fact I think I am way off. I wan to calculate weeks of stock on hand assuming an opening value of stock and assumed sales levels.
For example 5000 opening stock on 1 Jan and sales of 1000 in Jan, 2000 in Feb and 4000 in March would be calculated as:
52/12*2+(5000-2000-1000)/4000*52/12 = 10.8 weeks
I have written an if formula to calculate this however it is cumbersome has limitations due to the number of if's that can be entered.
Count Weeks Delayed
Sep 19, 2009how create a formula in D2 to D27 using the date A1 = (A2 to A27), and calculate how many weeks delayed comparing the value of Cum Actual (B1) if it less than, equal or greater than the value of Cum Planned (C1).
Sheet1
ABCDE1Week26-Jul-09Cum ActualCum PlannedDelayed2105-Apr-09 - 83.27
3212-Apr-09 - 237.00
4319-Apr-09 - 416.36
5426-Apr-09 - 570.09
6503-May-09 - 672.58
7610-May-09 - 826.31
8717-May-09 667.16 1,005.67
9824-May-09 667.16 1,244.25
10931-May-09 667.16 1,431.81
111007-Jun-09 667.16 1,646.79
121114-Jun-09 908.16 1,831.06
131221-Jun-09 1,345.16 2,046.04 ..............................
Calculate Number Of Weeks Stock?
Mar 12, 2014Trying to think of a formula that will tell me the number weeks stock I have,would like to put the formula in h6 and copy across giving me the weeks stock
Green line is sales, yellow balance stock
EX1.JPG
Counting Weeks INVOLVED Between 2 Dates
Nov 3, 2008I've been having problems coming up with a formula that will take a start date and an end date and come up with the number of weeks INVOLVED within this date range (each week being a Sunday through Saturday).
My problems is that the start and end dates could be any day of the week and not necessarily the same day (meaning divisible by 7 doesn't always work). I tried using ROUND((A2-A1)/7,0)+1 where A1 was the start date...and A2 was the end date.
The problem I had was if I picked a Monday as the start date, and went 12 days out (The saturday of week2)...it came up stating 3 weeks were involved - AND if I selected a Friday start date and picked the following monday in week 2 - the result was 1 week involved when 2 different weeks were involved.
How To Show Number Of Occurrences In Last 6 Weeks
Oct 12, 2012I am trying to create a table that will show the number of times a registration number has occurred in the last 6 weeks.
I can get it to display the values if I manually enter the rows that are in the last 6 weeks but I would like it to do it for me so other people can view the data without having to change values etc.
so Basically I have the date an entry was made in column B and the registration numbers are in column M. I have tried various combinations of COUNTIFS, SUM(IF and DCOUNT but to no avail! It either returns a 0 or an error.
BTW I am working with
=COUNTIFS('Event Tracker'!$M$605:$M$4999,A2)*('Event Tracker'!$B$2:$B$5000>=VALUE(O2))
*Where cell O2 is the date 6 weeks ago* at the moment which returns a 0 or the manual formula which is
=COUNTIF('Event Tracker'!$M$605:$M$4999,A2) where M605 represents the first entry 6 weeks ago.
How To Calculate Average Sales Of Last 10 Weeks
Dec 3, 2012In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
