Finding Current Month Total Sales Using Current Month To Date Sales In Formula / Macro?

Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

View 1 Replies


ADVERTISEMENT

Create Year To Date Sales Comparing 4 Years Month By Month?

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

Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)

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

Total Sales Of Every Month

Jan 14, 2013

I got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.

The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.

So in the new sheet I will have

Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on

View 5 Replies View Related

Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.

Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....

View 6 Replies View Related

Automatically Bold And Highlight The Current Month’s Total And Month Name

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

Formula To Calculate Sales Tax From Total Sales

Jan 4, 2005

I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.

View 9 Replies View Related

Macro To Save Files In New Folder With Current Month And Date

May 6, 2014

I have this existing macro which saves each tab into a separate excel file. However, I'd like for it to also save them together in a new folder using the current month and date (named: QA Files May_05.06.14). The month and date would change according to current month/date. How would I incorporate that into this code? I am not good with macros.

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.Name = Range("D1").Value
Next

[Code]...

View 4 Replies View Related

Trying To Add Total Hours Worked In Current Month

Jan 22, 2009

I'm trying to figure out what is wrong with this formula. =(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),0),W14:W4995))-(SUMIF(Q14:Q4995, "<="&EOMONTH(TODAY(),-1)+1,W14:W4995)). I've got a cell that adds Total hours worked which pulls from the same column of entered data as the formula above and that cell works. My hours for the month however just shows up as zero. If I try and edit the formula or even just highlight it to copy it and then tab out of the cell this shows up...

1/0/00

If I undo the highlight and tab it will go back to showing zero. I've checked my dates that I entered and they are correct. I'm at a loss as to how to fix this formula.

View 4 Replies View Related

Formula To Sum Sales Over 12 Month Period Not Based On Calendar Year

Mar 27, 2014

I am writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.

If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.

View 12 Replies View Related

Macro: Check CheckBox Is True, Current Date For Day/Month, Then Sum TextBox & Cell

May 28, 2008

I 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.

View 9 Replies View Related

Pivot Table: Calculate Percentage Of X Sales To To Total Sales

Feb 20, 2008

See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

View 2 Replies View Related

Current Month: Column B Equal To The Current Month Adding The Day In Column A

Sep 28, 2009

I have the following data:

column a: column B:
1
7
9
25

I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:

column a: column B:
1 09/1/2009
7 09/7/2009
9 09/9/2009
25 09/25/2009

View 3 Replies View Related

Find Value Corresponding To First Date In Current Month

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

Intersection Of Date Range And Current Month

Jan 9, 2007

I need to compute the number of days that a given range of dates has in common with any given month. So an example might be:

Cell A1: Range Start Date (say it's 1/1/07)

Cell A2: Range End Date (say it's 2/15/07)

Cell A3: Month Indicator (say it's 2, meaning February)

Cell A4: Days of Intersection (should be 15 in this case, meaning that 2/1-2/15 were the days of February that were also in the range 1/1-2/15)

Cell A4 is what I'm trying to create the formula for.

I'm in Excel 2000 and am having trouble installing the add-on.

View 9 Replies View Related

Get Month & Year X Months From Current Date

May 3, 2008

I am trying to get mmm-yy date format for the next 2 consecutive months from now. I used the following code for that reason, but I am not getting.


strDate = Format(DateAdd("m", 1, Date), "mmm-yy")

strDate = Format(DateAdd("m", 2, Date), "mmm-yy")

However if I use this, I get the current months date correctly (like May - 08)


strDate = Format(DateAdd("m", 0, Date), "mmm-yy")

View 9 Replies View Related

Comparing The Same Month Sales Between Two Different Years?

Jun 6, 2014

I am trying to create a function where the user can type in a single month, and 2 different years, and it will calculate/compare that single month from the 2 different years entered.

For Example: I type in January, and I type in 2014 and 2013. The excel formula will spit out whether January 2014 is over/under January 2013.

See attached for my sales report. You can see where I left some space for the requested function.

as it is the last change I have to make to the sales report.

View 5 Replies View Related

SQL To Excel - Stock Sales By Month

May 25, 2008

I am fairly new to VBA / Excel programming. I have been trying to write a report out of excel from our company DB (SQL2005). The database is run by our frontend accounting application - so i cant mess with it at all, must only run queries.

I need to pull the last 24 months of stock sales data(by stock code or category) out of our DB into excel by counting transactions on Customer Invoices / credits. Into a table as follows..
Stock Code--Month1-Month2-Month3
ABC1----------43------33------19
ABC2-----------2------10------25
I have managed to make a script that fullfills this need but it takes about 15 minutes to run(Due to having to loop many times per item/ per month)....
I was just wondering if anyone had any tips / advice on different ways to do this..??? Ive had a quick look at Pivottables but havent gone very far in, maybe they are the answer, but this amateur does not know.

View 10 Replies View Related

SUM A Range Of Sales Based On Month

May 6, 2006

I am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month

For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on

View 9 Replies View Related

How To Reference The Month Preceding Current Month

Jul 18, 2012

the MONTH(NOW()) Function, as I need it to reference the month preceding the current month.

View 3 Replies View Related

Sales Report From Sales Data Sheet If Select Date

Feb 4, 2013

I wanted to get the data from data sheet where i have entered the data manually for sales what have done

Now I wanted is when i enter date i should get the product and price in other sheet

Example:

If I enter date in a1 cell

I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet

And c1 - c10 price (if i sold 10 item @that price)

View 4 Replies View Related

Formula To Recognize Month And Current Year

Mar 23, 2009

finding a code that would automatically insert various phrases into a column depending on whether or not the person in that row was due to renew their membership in our organization. The code I received works fantastically, but I've come across one snafu:

View 3 Replies View Related

Return Current Month Using Excel Formula?

Feb 22, 2014

get the current month name in a Cell A1 and Next month name in Cell B1. what formula should I used? This will be a part of the macro that I'm creating.

The macro will be use every 11th to the last day of the month so I want the formula to be dynamic enough to work in each day the macro will be use.

Expected result using the current Date Today

A1 B1
February March

View 2 Replies View Related

Date In IF Statement - Get True Result To Be First Of Given Month Of Current Year

Feb 29, 2012

I've got a formula that has numerous nested IF statement and am trying to get the true result to be the first of a given month of the current year. Here's my formula:

Code:
=IF(Setup!$B$3="X","1/1",IF(Setup!$C$3="X","2/1",IF(Setup!$D$3="X","3/1",IF(Setup!$E$3="X","4/1",
IF(Setup!$F$3="X","5/1",IF(Setup!$G$3="X","6/1",IF(Setup!$H$3="X","7/1",IF(Setup!$I$3="X","8/1",
IF(Setup!$J$3="X","9/1",IF(Setup!$K$3="X","10/1",IF(Setup!$L$3="X","11/1",IF(Setup!$M$3="X","12/1",""))))))))))))

Everything works, save that the cell becomes 1/1 with no year at all. I've messed around a bit with TEXT and various DATE/MONTH/YEAR functions on this one, but can't seem to get it correct.

View 6 Replies View Related

Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

View 10 Replies View Related

Calculate Number Of Days Between Two Date Within A Current Month Including End Date

Apr 2, 2009

I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

View 9 Replies View Related

Pivot Table Show Selected Month And YTD Sales

Sep 18, 2009

I have created a Pivot Table to sumarize the monthly sales data.

Is it possible to select just one month and show the YTD sales?

e.g. We are in September, I select August the Pivot Table should show the August sales column and YTD (Jan to August) sales column.

View 9 Replies View Related

Formula: Current Month Based On System Clock

Nov 30, 2009

Is There a formula for current Month based on system clock....

View 10 Replies View Related

Count Current Year Month Wise Entries Formula

May 9, 2014

I use this formula to count number of entries month wise.

Following example is for Month of Feb.

How can I convert this to count entries for current year ( of course month wise).

View 9 Replies View Related

Excel 2010 :: Using Current Month / Date And Time In A Folder Path In VBA Code

Feb 4, 2014

I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.

1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …

2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved