Excel 2013 :: How To Add Date Range
Mar 4, 2014
I have a big spreadsheet with IT / Finance data. Below is the formula, I currently have. I am summing column Y in Tab Savings Data Detail, where column A does not equal Parent, and The Value in Column AK on the Savings data details tab matches the value in current tab for cell B26 and where column G on the savings detail tab equals Transition to IT. I am adding that value to the second sumifs statement essentially the values in column X on the savings detail tab with the same criteria.
Now I need to add to each of these formulas. I need to sum only the values where the date value in column W on the data details tab is greater than or equal to 01/01/2013 and less than or equal to 12/31/2013
find the easiest way?
=SUMIFS('savings data details'!$Y:$Y,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")+
SUMIFS('savings data details'!$X:$X,'savings data details'!$A:$A,"Parent",'savings data details'!$AK:$AK,$B26,'savings data details'!$G:$G,"Transition to IT")
View 1 Replies
ADVERTISEMENT
Jan 30, 2014
I need to set the dates on a tracking sheet to turn red after that date passes, I have tried using conditional formatting - Highlight cell rules - Greater than, but this hasn't worked.
It is excel 2013,
View 8 Replies
View Related
Apr 3, 2014
Looking to add automatic date stamp to a column of cells each time their corresponding row or specific cell in that row is modified.
I'm using Excel 2013.
View 1 Replies
View Related
Jul 11, 2014
I am rather new to excel and currently using excel 2013.
I have a worksheet with column A ( data group) Column B ( Sent date with MM/DD/YY) , Column C Aged Group {using this formula =IF(B2="","",(TODAY()-B2))} , Column D status ( if the record is open or close).
I would like to create a table that can segregate the data group and how many days it has already aged. And then group them with different aged group. Refer to the attached screenshot.
From:-
Data GroupSent Date AgedStatus
Quotation 06/06/1435Open
Invoice 07/11/140Open
invoice 05/01/1471Open
PO 07/01/1410Open
PO 07/05/146Open
SO 06/30/1411Open
Quotation 05/18/1454Open
SO 05/05/1467Open
Cash Note 07/03/148Close
To:-
Data Group0-15 15-3030 above
Quotation
Invoice
PO
SO
Cash Note
testtest.xlsx
View 6 Replies
View Related
Apr 12, 2014
I'm having trouble getting the dates i want on the horizontal axis on a chart in Excel 2013. I want 12 dates along the axis starting at 01/01/2013 then each month until 1/12/13 however as you can see the dates are 01/01/1900.
View 10 Replies
View Related
May 8, 2013
Is there a formula which places the date In A5 whenever text is entered in A2 - Excel 2013
View 9 Replies
View Related
Nov 21, 2013
I just installed Office 2013 Pro. In my Office 2010 Additional Controls toolbox, the control toolbox has a Date and Time Picker(DTPicker1) . I cannot find it in the Additional Controls toolbox in Excel 2013. I certainly hope they did not choose to not make it available in Excel 2013 - this is an extremely important control for my Excel spreadsheets as it allows inputting a date range on a userform with DTPicker1 and DTPicker2 and generating reports using VBA in conjunction with Autofiltering code on a Generate Report button.
View 8 Replies
View Related
Aug 10, 2014
Here is a very simple workbook/sheet with some simple date arithmetic.
I keep getting the warning that there's a circular reference in C2.
Either I need to suppress the warning so vba loops can run or PREFERABLY I'd get rid of the "circular reference".
Run with Excel 2013.
View 14 Replies
View Related
Apr 5, 2014
I am working with DatePicker calendar. I have two userform with date entries. In one userform I will have 2 textbox to enter dates. In the other one I will have 4 dates to enter. I used to have Excel 2007 with Calendar Control and used to work fine but had to make multiple calendar to control individual Textbox in multiple Userforms.
I am looking for a way to, If a userform is selected to trigger only the textbox name that is within that userform. If I add more entries the code adds automatically the date from the previous userform in the next one.
[Code].....
I was thinking that this would work but it still adds the date to my frmdashboard userform if I am in my frmdata userform.
Is there a way to trigger only the userform that I am currently in?
Using Excel 2013.
Note: I also have a "Clear All" VB Code that will delete every entry in my userform on Initiate but I am looking to not go this path.
View 5 Replies
View Related
Aug 31, 2013
Here is an example of what I am trying to do in E2013.
The orange Bin is my input. It want that to return the column associated with the # as show in orange output column. I think this is an index command? I cannot seem to make it work.
Also - is there an easier way to insert sheets other than a screen grab and photos?
View 5 Replies
View Related
Dec 26, 2013
Using Excel 2013.
I have two columns of numbers. For a row, if the right number is greater than the left number, then color it green, if it is less then color it red.
e.g.
A1 = 100 B1 = 101, then B1 gets colored green
A2 = 100 B2 = 99, then B2 gets colored red
etc.
I have clicked on conditional formatting with A2 selected, then chose A1 for values greater than and chose green. I then copied the formula for all rows. I then did the same for values less than and chose red.
It is not coloring the cells correctly. On some rows when B
View 9 Replies
View Related
Jun 11, 2014
I am using Excel 2013
I need to print to a printer that is not the default printer, a range of Cells e.g. B4:L28 on Sheet Print Out.
The code is to be added to a VBA routine that already exists that collects & arranges the data on the Print Out sheet from other sheets in the workbook. This routine is assigned to a button on another sheet.
View 2 Replies
View Related
Feb 2, 2013
I need to place a date and time in a cell 14/9/2013 6:00 PM
Tried several formats under custom format without success.
Also need addition to following macro to enable it to start on opening of workbook
Code:
Sub Macro1()
Do
PauseTime = 1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
[Code]...
View 8 Replies
View Related
Jul 9, 2014
I would like to mirror a range of cells between two sheets in the same workbook, so that if the data is manually input into one of the cells in range (E5:H11) in 'Sheet 1', it is automatically updated in the corresponding cell in (H33:K39) of 'Sheet 2', and vice versa.
Using Office 2013. T
View 6 Replies
View Related
Nov 5, 2012
I am using windows 7 64bit sp1 and excel 2010 32bit sp1
I am trying to fix this formula (it takes a range in a unique column, and multiple row:
Code:
=IFERROR(INDEX($B$1:$B$6,SMALL((IF(LEN($B$1:$B$6),ROW(INDIRECT("1:"&ROWS($B$1:$B$6))))),ROW(A1)),1),"")
from here
I want to make it work on my array in a row, like this: (i need to remove the blank cells and the output have to be still in a row)
value1
empty cell
empty cell
value 2
value 3
empty cell
output:
value1
value2
value3
I need a function becouse I have dynamic table with something like 10k rows and 50 columns.
View 2 Replies
View Related
Mar 15, 2013
I have a spreadsheet that keeps track of the equipment we have on rent. Whenever we have a new rental, I insert a row and enter the information. I have several conditional formats I am applying - (1) making the font a light grey so that it is hard to see on any rental that has been called off, but invoicing isn't complete on, (2) making the font red on any item that is within 3 days of the term, and highlighting any row that isn't showing an invoice in over 45 days. When anything is completed (rental has been called off, and the final invoice has been approved), I cut the row out and put it in a sheet entitled "Closed Equipment". When I cut out closed records or add new records, it will occasionally mess up my conditional formatting by only referencing one row. Additionally, I don't want the conditional formatting to transfer over to the "Closed Equipment" tab.
Would this be easier to keep clean and straight if I used VBA? Or should I just continue to occasionally check my conditional formatting and clean it up? I thought if I used a Named Range instead of cell references, it might work better, but entering named range "On_Rent" converts to the cell range ($A$4:$AA$194).
I am using Windows 8, Excel 2013. The file is on a network drive, and others in the office can look at it (they all run Windows 7, and either Excel 2010 or 2007), but they look at it so seldom.
View 1 Replies
View Related
Jul 13, 2014
I have Excel 2013. I would like to use the code below but I need it to let me select the range instead of specifying it in the code, then pop up a form into which I can fill in what I want it to search and what I need it replaced with.
[Code] ......
View 3 Replies
View Related
May 30, 2013
I currently have this beast of a code running in Excel 2013:
HTML Code:
Sub OBTAIN_RAW_DATA_MACRO()
Dim NextCol As Long
'Copy data
Workbooks.Open Filename:= _
[Code] ........
However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.
Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?
View 4 Replies
View Related
Mar 19, 2014
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
View 2 Replies
View Related
Jul 19, 2014
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
View 7 Replies
View Related
Apr 14, 2014
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
View 1 Replies
View Related
Jun 17, 2014
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
View 1 Replies
View Related
Dec 23, 2013
I just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.
View 1 Replies
View Related
Apr 15, 2014
Using EXcel 2013, Windows 8
I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc
Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?
Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.
View 2 Replies
View Related
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
Aug 5, 2013
I have a column of dates in Col H with associated values in Col I. I need to specify a date range in Cell I1 and I2 such that the row number for the first encounter of the first date is placed in Cell I3 and the last encounter of the end date is placed in Cell I4. For example, with this data when I specificy 4/2/13 and 4/3/13, I would like to get a 19 in Cell I3 and a 53 in Cell I4.
Matty supplied the formula in Col K for each of these cells. They worked well in the application that I supplied earlier (different locations for these variables), but my real application is as shown here, and these two formula give the incorrect results shown. Both of these formula are arrays.
Excel 2010HIJK14/2/201324/3/2013310=MATCH(I1,INT(H10:H5000),0)+1444=MATCH(2,1/(INT(H11:H5000)=I2),1)+1536789X10 114/1/13 1:366.97124/1/13 2:04134/1/13 2:04144/1/13 4:563.95154/1/13 4:573.27164/1/13 5:165.55174/1/13 5:172.35184/1/13 10:30194/2/13 14:00204/2/13 14:59214/2/13 15:01224/2/13 17:192.81234/2/13 17:191.59244/2/13 17:252.14254/2/13 17:262.05264/2/13 21:07274/2/13 21:07284/2/13 21:11294/2/13 21:11304/3/13 1:38314/3/13 1:38324/3/13 2:10334/3/13 2:10344/3/13 4:24354/3/13 5:152.84364/3/13 5:154.11374/3/13 5:173.45384/3/13 5:173.24394/3/13 9:35404/3/13 9:35414/3/13 9:59424/3/13 10:01434/3/13 13:36444/3/13 13:37454/3/13 13:41464/3/13 13:42474/3/13 17:124.03484/3/13 17:133.62494/3/13 17:15504/3/13 17:15514/3/13 21:12524/3/13 21:13534/3/13 22:214.41544/4/13 1:52554/4/13 1:52564/4/13 1:53574/4/13 1:53Ppk Raw Data (2)
View 2 Replies
View Related
Jan 1, 2014
Covert number to Date 20131126 to 11/26/2013
View 7 Replies
View Related
Jul 2, 2014
Working in Excel 2013......... I take 3 readings per day, I would like to see the daily average per day
In cases where the data is blank or "0", just average the 2 readings.
Sample Data
Date Speed
6/1/20142560
6/1/20142550
6/1/20142558
6/2/20142554
6/2/20142537
6/2/20142532
[Code] ..........
View 4 Replies
View Related
Nov 28, 2013
My and a work college needed to combine our separate excel worksheets into a single document.
Office 2013 didn't have a function to "import sheet from file" so we used open office to import my .xlsx worksheets.
After we finished importing we exported the final workbook as .xls (so I could open it).
After opening the workbook on my pc (excel 2013) i notice some of the sheets no longer have column headers, but the row headings still exists. (No ABCD, only 1234)
Also I am unable to use features such as "Freeze Pane"
I suspect this was caused by importing and exporting through open office?
View 4 Replies
View Related
Dec 18, 2013
Tried a macro with no luck, I want to save workbook to PDF and have it named by the value in A3. I want the Dialog box to pop up so I pick the folder?
View 6 Replies
View Related
