I 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 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.
I'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.
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'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
In column B is a list of dates, which are broken out by fiscal year (FY=7/1/ to 6/30). Column C is a list of dollar amounts. Cell F3 is the query ‘start date’ Cell F4 is today's date, which will be the formula: =today()
Using a date range of F3:F4, I'm trying to get each cell bordered in red to query the dates in column B for each FY; then display the matching sum from column C.
For illustrative purposes I went and manually determined what the values should be displaying as of 7/22. Here are a couple of examples of how I am trying to get the formula to work.
Example 1 FY’08: Start date- 7/1 End date- 7/22 Searching cells B1:B94, cells B1:B13 fall within the start and end date parameters listed above. Sum of cells C1:C13 is $45,112.00, which should display in cell F6.
Example 2 FY’09: Start date- 7/1 End date- 12/13 Searching cells B95:B222, cells B95:B149 fall within the start and end date parameters listed above. Sum of cells C95:C149 is $150,873.03, which F7 should display in cell F7. How to I type up this formula; its way more challenging that I first thought!
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.
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.
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.
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.
I have an 'existing results table' as per my attached sample. From this table I need to create a list of 'sold' for each annual date range - i.e. I need a separate list for items sold within that date range. see my example in red. *Note. I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
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.
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.
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.
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.
I 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............
i have a spreadsheet containing to columns "Date from and "Date to". the sheet is recording the attendance of staff. how can i create a pivot table to display how many days staff attend across months. i cannot us the first date "date from", as this will display all the days attended
I want to see if the people on list one are on list two. I need to come up with a formula that gives TRUE for each person on list one who is on list two and false for each person on list one who is not on list two. Several caveats: 1) there are people on list one who aren't on list two and vice versa, 2) the people have several defining characteristics which need to be met, and 3) dates are involved.
With respect to point three, TRUE should only appear if the people on list one are on list two and if they were employed by the organization on a specific date (ex: 3/5/1996); in order for the TRUE statement to appear in the G column, the person should not only be on list two but also be employed on a specific date. The specific date should fall within the start and end employment contract.
I want to create a function that will check 2 user input dates (and anything in between) to see if it lies in a defined tax year. I will define the tax year in Cells A1: 6/4/2009 and A2: 5/4/2010 and ask the user to input dates in Cells B1 and B2 to check if any of these dates (or anything in between) falls between A1 and A2.
For example: Tax Year is 6th April 2009 - 5th April 2010, Cell A1 is 6/4/2009 and A2 is 5/4/2010. User inputs 2nd March 2009 in Cell B1 and 10th May 2009 in B2. Because the 10th May falls in the tax year the output should be "True". I can Excel to check one user defined number (Cell B1) using this =IF(AND (B1>=A1,B1<=A2),"Yes","No") but not sure which route to take to check 2 numbers B1 & B2 and anything in between.
