I am attempting to calculate at MTD total for a previous month. For example today being the 16th, I am needing to calculate the MTD total from April 1 - April 15? I have a spreadsheet that has the totals for the individual days but whenever I try a formula it returns the entire month total. Here is a formula that I have used
SUMPRODUCT(SUMIF(Overall!$DZ$18:$DZ$382,MONTH(B3),Overall!$D$18:E$382))
I have a large Excel project that I am working on and it is in it's final stages......i have a table with week 1 to week 5 on the top.....I hoping to be able to change this across multiple sheets to the week ending date for that month? so if on the first sheet someone selects april 2006 then week1 and so on would show 07/04/06
View 2 Replies View RelatedI need some assistance taking dates entered in a userform and applying them to the sheet 'Completed_Report' cells Q1 and R1. The userform code that stores the input values is:
View 2 Replies View RelatedI have attached link to a resource planner I have cobbled together and am stuck on the last piece of code. The "Assign Project" button needs to take the project and resource from the drop-down lists and populate the column of that resource with the project name between the dates that have been selected.
Resource Planner: [URL] ....
I'm using Excel 2010 and Windows 7
I would like to automate a sheet to report statistics of projects based on a single cell that is the year that a user manually inserts. I've got a separate worksheet with all dates manually set up for next 10 years and would like it to be read by formulae in a reporting worksheet based on the year that user enters.
So if I specify year 2014/15 (financial year), I would like the cell C1 under "Reporting" sheet to display 1/07/14 predetermined in "Dates" B3, if I specify 2015/16, the cell C1 will read 1/07/15 from "Dates" B6 and so on.
I could do it with "IF" function but there will be too many nested IF functions in a lot of cells (hundreds if not thousands). This may make my file size very large.
Attached is a simplified example of what I mean.
We have arrival dates and departure dates for guests and would like to figure out the number of meals needed on a particular date.
Named ranges: NumGuests _ArrivalDate _DepartureDate _CheckInTime _CheckOutTime
Need number of breakfasts
Number of Lunches
Number of Dinners for a given day of the week.
So if a guest is here say Jan 25 arriving after 9am, staying 2 nights they will have Lunch and Dinner on the 25th, BLD on the 26th and BL on the 27th. Since by default most arrive after 9 (miss breakfast), we can eliminate the checkin time for the breakfast calc and make the first one for the next day.
There might be other guests staying through that time period. The ******* would like to know how many meals they will need to prepare for the week.
Windows XP, Excel 2010
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
Basically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................
The following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)
I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100
Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?
I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.
View 2 Replies View RelatedI have a masive table of dates (the date is created via a if formula)
what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.
Dates To Test 14/08/2026 19/08/2026
Results
14/08/2026
15/08/2026
16/08/2026
17/08/2026
18/08/2026
19/08/2026
Table..............
i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies View RelatedI have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail.
Also i do not get the option to convert text to number
I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:
'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1
For DBRow = MainOrderRow To LastRow
' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................
I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters:
In column L I would like to represent the following
if column "B" is "A" add 38 years to column "J" dates are represented as TEXT
if column "B" is "B" add 35 years to column "J" "
if column "B" is "C" add 30 years to column "J" "
if column "B" is "d" add 30 years to column "J" "
Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only.
I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.
I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.
It seems that the worksheet is not activating. Please take a look and let me know what I can try.
Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.
Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.
Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............