Over 1 million tech questions and answers.

Solved: Excel 2010: Problems with SUMIFS and dates

Q: Solved: Excel 2010: Problems with SUMIFS and dates

Hi there,

In the attached spreadsheet, I'm trying to sum up the sales of a product line, by a particular sales rep, product and date. I'm getting an answer, that at first glance looks correct, but if I total up the yearly sales using an independent formula, I arrive at a different figure.

I think the problem is stemming from sales on the month-end dates, but I can't work out how to correct it. Any help would be much appreciated.

Note: At first glance the spreadsheet seems to contain confidential data. I'm happy it's not.

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel 2010: Problems with SUMIFS and dates

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel 2010: Problems with SUMIFS and dates

Read other 9 answers
RELEVANCY SCORE 99.2

Hi Guys,

The function itsslef is no problem but what I would like to achieve is the following:

I have the following formula which works perfectly:
Code:

=SUMIFS(Computación!$BB:$BB;Computación!$BK:$BK;Tabla!C$8)

What I would like to achieve is that the sheet which is named Computación! be variable.

I have a dropdown validation list in cell C6 which has a list of let's say 10 sheet names

So when I select another sheetname I would like the reference that now says 'Computación' be the value whihc I select in the dropdown list

How would I do this?

I tried [$C$6] I named the cell C6 and tried that but I only get an error that the formula is incorrect

I hope I've been able to explain myself a little
 

A:Solved: Excel 2010 Need help with a SUMIFS function

I found it, I had tried INDIRECT but forgot something.

The forumla is now
Code:

=SUMIFS(INDIRECT("'" & SECTOR & "'!$BB:$BB");INDIRECT("'" & SECTOR & "'!$BK:$BK");Tabla!C$8)

And it works
I'm closing this and marking it as solved.
 

Read other 1 answers
RELEVANCY SCORE 99.2

Hi,
I tried something using the SUMIFS() but it does not really work as I expect it to.
I.ve attached a screenhot and a file with some sample code.
There are several columns
(A)DAYNAME, (B)DATE, (C)WEEKNR, some blank columns (H)STARTTIME,(I)ENDTIME, (J)SUMOF HOURS, (L)WEEK SUM
there is namedrange lunch in K1 which subtracts that time when SUMOF HOURS is calculated
All this works fine.

Column L:
In this column I have forula using SUMIFS that adds all the values in RANGE(Column J), RANGE(Column C), value for that row in C

Sampe formula in L4 which is copied down to Row 29

Code:

=IF(AND(VALUE($C4)>0,$J4<>""),SUMIFS($J$4:$J$29,$C$4:$C$29,VALUE($C4)),"")

The default font color iw white on white and the coditional format makes it BLACK pwr week group
So all Week 17 is added together
All Week 18 is added together
I use the column Q as extra test to set it TRUE or FALS so when TRUE the font is black (visible) in L

The problem is that the coloring works fine but the Week does not sum except for the first weeks per block
Week 17 and week 19 are doing just that but week 18 is 00:00
I cannot figure it out, I could write this out in a macro and make it work but a formula should make this work too.

What am I not doing right?
 

A:Solved: Excel 2010 SUMIFS not doing what I expect

Hi
The correct answer for L8 is 24 but it is formatted as "Time" so 24 Hrs is actually 00:00:00 in the 24 hour clock. To get the number of hours you need to set the format as Number and change your formula to

Code:
=IF(AND(VALUE($C4)>0,$J4<>""),SUMIFS($J$4:$J$29,$C$4:$C$29,VALUE($C4))*24,"")
the "*24" converts time to number of hours
 

Read other 2 answers
RELEVANCY SCORE 83.2

Hello,
I'm without any success hardly trying to modify (as begginer I'm still unable to create) some macro's I found in this forum in order to send data from Excel sheet into a specific public (or not if it's too heavy) calendar in Office 2010.
Import would take place with a push of a button and duplicates should not be allowed...

Even a simple line like: Dim olApp As Outlook.Application gives an error ...Maybe I'm getting tired...

And how should I modify those lines in order to select a specific Calendar (using Folder(Calendar name) in place of GetDefaultFolder in the lines mentioned here under?
Set olNS = OL.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderCalendar)

If someone would be kind enought to give me a help, here are some details about my Excel sheet:
Column A "Company", Column D "Date soon", Column G "Date Late"
Column A is fully completed, but columns D and G have some lines without any date...
 

Read other answers
RELEVANCY SCORE 76.8

Hi

I am trying to ount the number of transactions between two dates and then to add up the amounts taken in each transaction between those dates.
I have two columns one with the date of the transaction and one with the amount of the transaction.

I need to count the number of transactions in a given time period then to calculate the average spend during that time period.

To calculate the number of transactions i am using the following where A899 is the start date and time and B899 is the finish start and time.

=COUNTIFS($D$2:$D$887,">="&A899,$D$2:$D$887,"<="&B899) this gives me a total of 73

then what i need to do is the add together the amounts of each transaction that takes place between those two dates, the amounts are in the column next to the date, i am using the following formula to get the total but all i get is this message 'The formula you typed contains an error'

This is the formula i am using, please does anyone know what the error is
=SUMIFS(E2:E887,$D$2:$D$887,">="&A899,{$D$2:$D$887,"<="&B899})

Thank you in advance for your help.
 

A:Solved: Excel SUMIFS formula that won't work

hi
it should work if you take out the squiggly brackets {}
=SUMIFS(E2:E887,$D$2:$D$887,">="&A899,$D$2:$D$887,"<="&B899)

there is also an AVERAGEIFS function you can use which would eliminate the need for the countifs.
=AVERAGEIFS(E2:E887,$D$2:$D$887,">="&A899,$D$2:$D$887,"<="&B899)
 

Read other 3 answers
RELEVANCY SCORE 74.8

I have a simple spreadsheet showing vehicle details, with expiry dates for registrations. I would like to use conditional formatting to have all dates that are 30 days or more ahead of today be green, for dates that are 1-29 days greater than today to be orange, and for dates that are the same as today or earlier to be red. I know the basics of conditional formatting, but can't get my head around all the 'ifs' involved. I'd welcome any help!

Thanks
 

A:Excel 2010 - conditional formatting for dates

I attached a sample with the formulas dispalyed in the cells to show
 

Read other 1 answers
RELEVANCY SCORE 72

Good morning,

I am battling with a formula that I have used before. I have created a Pivot Table, but I need the data in it transposed, and to do this I am using the SUMIFS formula, as per the attached spreadsheet. (If there is a better way to do this, I'm all ears.) The problem I am facing is that clearly I am using the formula wrong, as I am coming up with 0 values all the time.

Any assistance greatly appreciated!
 

A:Solved: Problems with SUMIFS

Read other 7 answers
RELEVANCY SCORE 67.2

Hi everyone,

I really need help with something that's driving me crazy. I am working on a spending report where I have a list of bank transactions on the 1st worksheet, and then subsequent worksheets are for each month of the year and other data on them.

I created this whole spending report at home on Excel 2007; however, at work where I need it I work on Excel 2003. I need help converting the below SUMIFS formula to a valid Excel 2003 version.

=SUMIFS('Master Spending Trans Summary'!C:C,'Master Spending Trans Summary'!A:A,'Jan. 2012'!A12,'Master Spending Trans Summary'!B:B, ">=" & "1/1/2012", 'Master Spending Trans Summary'!B:B, "<=" & "1/31/2012")

Whenever I've tried to convert it using SUMPRODUCT or SUM(IF, I keep getting either #NAME? or a #NUM! error.

Anyone who knows how to convert this formula, please please please help me!!

Hopefully someone can help convert this quickly, as I need it soon!

Thanks in advance!!
 

A:Help Converting SUMIFS to Excel 2003?

It's an old postin but I found this link that will help you out

http://chandoo.org/forums/topic/converting-sumifs-to-excel-2003
 

Read other 1 answers
RELEVANCY SCORE 64.4

hello!

I'm using a SUMIFS to pull account data based on several criteria (market segment, month, status, etc.) and sum the revenue by account. The vast majority of the account names work fine, but some of the names yield inaccurate results.

For example, when I perform a SUMIFS where one the the criteria is "The Dow Chemical Company" the results returned don't satisfy all of the other criteria (for example they pull from the wrong wrong status). However, when I switch both the criteria and the values in the criteria range to "Dow Chemical Company" the SUMIFS function works correctly.

Is there some sort of character limit that I'm bumping up against?

Thanks,
Wild Bill
 

A:Excel SUMIFS results vary based on name (value) of the items in the criteria range

A couple of things I would check for:
1. Are there any erroneous spaces which would mean that "The Dow Chemical Company" isn't being matched against its term in the SUMIF
2. Have you ensured you've locked the cells ($) for the name lookup range?
You haven't posted a sample to check, but I'm attaching a sample for you to see.
 

Read other 1 answers
RELEVANCY SCORE 64

I would like to build a spread sheet so when I enter a date the adjacent cell will have a date according to some rules. For example

Dates entered between Jan-01-2013 and Mar-31-2013 the adjacent cell will have a date of July-01-xxxx (where xxxx is the same year as the entered date. Can someone help me out with this.

thanx
 

A:Solved: excel dates

Read other 9 answers
RELEVANCY SCORE 64

Hey
I have imported data from an outside database and it brought in the dates seperated. The information is in columns and the day, month (which is in text), and year each have their own cell. There are hundreds of dates. Due to space considerations I need each date to only take one cell. Is there any way to do this without having to do it by hand?
 

A:Solved: Dates in Excel

Not sure which version of Excel you are using, but am assuming 2007. I will also mention what to do if you are using 2003 or earlier

Create a blank worksheet and do the following so that you can see how the following formula works and then how to convert it as a value rather than a formula

In A1 type 24
In B1 type June
In C1 type 1990
In D1 type =DATEVALUE(CONCATENATE(A1,B1,C1))

Assuming you have similar data in the Columns A, B and C, copy the formula in D1 down column D until you have a formula for each row of data in A, B, C

Select all the formulas in column D and click on Home Ribbon tab ans click on the Copy Icon to place it in the clipboard. 2003 and earlier Edit, Copy

Whilst those cells are still selected click on Home ribbon tab and click on the little down arrow just under the Paste button and select paste values. 2003 and earlier Edit, paste Special, Values OK

Your data in column D will now be as a date (not a formula) which in the background is treated as a number, which then allows you to do calculations on the dates.

Now select columns A, B and C and delete those columns

You are now left with column A and the dates in single cells.

Hope that helps
 

Read other 3 answers
RELEVANCY SCORE 64

Quick question.

Lets say I have a date.

11/01

Is there a formula that would return and IF statement?

For example

I am looking to all the dates between 12/15 and 01/15

I have tried to create a formula using the IF statement, but cant only get the first half.

=IF(A1<=12/15,"TRUE","FALSE")

But how do I get the second half for it to check if if the date is less then 01/15? every time I try I get an error.
 

A:Solved: Excel Dates

Howdy. If I understand correctly. Be sure to check year, since the year will be in the cell whether it displays or not.

=IF(AND(A1<=12/15,A1>01/15),"TRUE","FALSE")
 

Read other 3 answers
RELEVANCY SCORE 64

Is there any way I can get a value produced that is the previous week day, ie not a weekend, that I can then export into notepad? To be run automatically everyday?
 

A:Solved: Excel dates

Read other 10 answers
RELEVANCY SCORE 64

I'm working on a filter to show which Drawing Changes are overdue by selecting check boxes that have certain overdue periods. (e.g. 1-14 days past due, 15-30 day past due, 31-60 days, etc.) I'm not very good at VBA but I'm using some code I have from a search form I used a while ago that has a date filter. I'm confident I can figure out the code for the filter, but I'm not sure how to say what I want to happen in VBA code. What I want the filter to read is this:

If 1-14 days is checked, include entries that (Due Date + 14days) >= Today's date
If 15-30 days is checked, include (Due Date + 30days) >= Today's date AND (Due date +14days) < Today's date
and so on.

I'm using Access 2010 on Windows XP

Thanks for any help!

Dobber9
 

A:Solved: Creating a Filter and Adding dates in VBA Access 2010

Read other 8 answers
RELEVANCY SCORE 63.6

Hi,
I know this is probably an easy question, but I've searched repeatedly on the web and I can't find advice there or in the forums because I'm missing some kind of terminology.

I want to be able to highlight multiple words in Excel and see the home toolbar open every time I open a spreadsheet.
Longer explanation:
I'm using Microsoft Vista 2007 with Excel 2010. I use Excel to make quite a few spread sheets to study for school, and I often have to selectively highlight multiple certain words by hitting ctrl and holding ctrl while highlighting the words. Next I would underline all of the highlighted words. About a month ago it was working fine.
Suddenly out of the blue whenever I try to highlight multiple words by holding ctrl in Excel it won't work. Also, previously when working in excel 2010 the top menu bar would stay on home toolbar with all the option listed, meaning I could keep tinkering with the font uninterrupted. Now after underling just one word at a time the toolbar keeps disappearing with the File tab highlighted green, and I have to keep clicking back to the home tab, then click underline. Have to repeat highlighting each individual word,, clicking home tab, and then underlining. Ugh!
I would love to hear some advice before I tear my hair out in frustration.
 

A:Solved: Excel 2010 unable highlight mutliple words & toolbar problems

Read other 6 answers
RELEVANCY SCORE 63.6

Afternoon everyone!

As the title suggests, I have some issues with formatting the x-axis of a column graph (the graph is to show air consumption rate over a period of 30 minutes) in Excel 2010 (the file in question is attached). As you can see in the attached picture, the graph currently has the x-axis formatted such that it starts in the middle of the ticks. Ideally, I am looking for a way to start "0" from the y-axis intercept without shifting the graph as is currently the case when changing the horizontal axis options.

Could you possibly help me sort this out? I need to present it for a university project and I'd like to have the graph presented in this manner. It would also help if I could learn the method so I can do this in future. Thank you for your help in advance!
 

A:Solved: Problems fixing horizontal axis on column graph in Excel 2010

Read other 6 answers
RELEVANCY SCORE 63.2

I'm working in Excel '03, I have 3 columns of dates. Opened, Due and Closed. I would like to sum the number of days it took to close an item (opened date + closed date), shown in another column, and then compare the Due date to the Closed date, if the Closed date is later than Due, have either the Due or Closed date be highlighted in red...

I am formating something wrong here, as it is looking at my dates as numbers... I really need to take more classes....

Thanks in advance, Skyie
 

A:Solved: Excel - Dates Question

Read other 7 answers
RELEVANCY SCORE 63.2

Excel 2007
I want to calculate the time (in years) between 2 dates ... so that a 27 month span would appear as 2.25 years.
Using the formula " =year(A1)-year(A2) " yields an answer in whole years. I want an answer accurate to 1 (or 2) decimal places.
Defining the cell with the above formula in it, as a "Number" with 1 (or 2) decimal places does not do it,
Any suggestions ?
Thanks
 

A:Solved: Excel - Time Between Dates

Read other 11 answers
RELEVANCY SCORE 63.2

I'm attempting to use the VLOOKUP function to look up dates, which I've successfully done before. This time around, VLOOKUP does not seem to be finding the dates I'm looking for, even though they exist and are an exact match. So here's a sample.

So my dates look like this:

Date/Time
9/1/2007 13:00
9/1/2007 13:27
9/1/2007 13:58
9/1/2007 14:00
9/1/2007 14:35
9/1/2007 15:07
9/1/2007 15:48
9/1/2007 16:00

Now I only want the values that have been reported on the hour, so my lookup values look like this:

9/1/2007 13:00
9/1/2007 14:00
9/1/2007 15:00
9/1/2007 16:00

My function looks like this:
VLOOKUP(B2,Sheet1!$B$1:$F$12422,3,FALSE)

Excel seems to be finding some dates successfully, but other dates it returns a #N/A. I have used an if statement to test some of the dates that are not being successfully found. So I use the function IF(A1=B1,"YES","NO"), where A1 is the date in the date in the table, and B1 is the matching date I am trying to find...and when I do this, I receive "YES", which to me suggests the dates are exactly the same, formatting and all, so Excel should have no trouble finding the exact match. I have also tried converting the dates to the serial number (i.e. 12/13/2006 20:00 = 39064.83333), and although the numbers match exactly, that has not solved the problem either.
Can anyone help me out?
 

A:Solved: Excel: Looking up dates using VLOOKUP

Read other 6 answers
RELEVANCY SCORE 63.2

I have attached the example document.

Would like to know if i entered in a known date, how would i have it add in a known remaining years.
if the years calculate by a point and not a month.

Ex. there are 12 years in a month. there are only 10 whole numbers in the calculation.

so if the computer gives me a remaining life of 2.5 years that would mean two years and 6 months.

but what if the computer gives me 2.2 remaining years? how many years and months is that?

And once the 2.2 is figured out how can i have it auto calculate that to the known date that i have to give
me a total date with the computer date and the know date?

Let me know if the example is not explained well enough.
 

A:Solved: Excel 2007 Help with Dates

You don't need your chart to figure out the decimals. Probably the easiest way to do it is:

=B22+365.25*C2

This multiplies 365.25 (the average number of days in a year) times your half life to get the total number of days represented by the half life. Then add that to your date. It may be off by a day or two (depending on the half life) since some years have 365 days and some have 366, but it should be very close. Hope that helps.
 

Read other 3 answers
RELEVANCY SCORE 63.2

I have two worksheets, Dates and R Dates.

The Dates worksheet has this weeks dates (12/8/2008 - 12/12/2008). The R Dates worksheet has future dates of when projects come due.

What is the easiest way to match the dates from the "Dates" worksheet and the "R Dates" worksheet?

For Example, if 12/8/2008 is found on both worksheets, I would like "Yes" to be entered in D5 of worksheet "Dates". If 12/8/2008 is not found on both, then I do not wnat anything to happen.
 

A:Solved: Excel Match Dates

Read other 16 answers
RELEVANCY SCORE 63.2

Hi all,
I've spent a good few days reading posts on this site it seems between everyone here there a whole host of knowledge!

I'm here as I am having trouble with an excel sheet and I'm unsure how to solve it.
I am using excel 2007 on my laptop.

I've searched both this site and google for a few days now but I haven't found anything that seems to be what I need.
There is a thread on here that was helped by Keebellah and that is the closest I can find to what I am trying to do however I can't seem to edit the code in such a way that gets it working in my sheet.

Let me explain what I am trying to do.

My sheet is a training tracker which has a column of names of employees and some dates.
Specifically the dates are: Ideal WK4 date, Ideal WK8 date, Ideal WK12 date. These all have dates inserted.
There is also a column next to each of these that either says completed, or is empty.

I am trying to get Excel to email me when one of these dates is 7 days away or less, unless the column says completed. Then I don't want an email.
I want the email to basically say:
EMPLOYEE NAME is due for their WK? meeting within 7 days. Please schedule this in.

The employee name and WK4/8/12 should be pulled from the sheet.

Logically speaking, this is what I am looking for...
IF M(ideal date column wk4) = less than 7 days, send email with row data. If O=Complete don't send

IF Q(ideal date column wk8) = less than 7 days, send email with row data. If S=Compl... Read more

A:Solved: Emails from Excel using various dates

Read other 16 answers
RELEVANCY SCORE 63.2

i am trying to make a spreadsheet that will tell me when a customer has not made a payment on an item in X amout of months. ex: i want the cell to turn red if it has been over 3 monoths since the customer has made a payent. basicly it is just conditional formatting on the cells. i don't know and can't figure out how to work with dates in the formula bar, especially display the current date

help would be much appreciated, TY
 

A:Solved: working with dates in excel

Hi there,

Dates are arranged by Serial Numbers. One whole number is one whole 24 hour period, so one day is 1, two days is 2, thus 0.5 works to be half of a day.

The formula for today's date is =TODAY()

So in your conditional formatting, you could use something like this ...
Code:
=A1<TODAY()-90
This assumes that A1 is the cell you are in and the one with the date in question in it.

Post back if you need more help.

HTH
 

Read other 2 answers
RELEVANCY SCORE 62.4

Hi,

I have a spreadsheet that calculates how long a contract has left to run, using the following formula:

=((EDATE(R5,S5))-TODAY())/30

where R5 is the start date of the contract, and S5 is the duration of the contract.

I need to make the spreadsheet think that it's June, and not today, but I'm struggling. Does anyone have a suggestion?

Thanks in advance!
 

A:Solved: Fooling Excel 2007 with dates

Answered my own question with a bit more fiddling. Just converted the date to a number and subtracted it.
 

Read other 3 answers
RELEVANCY SCORE 62.4

Hi, before you all tell me how easy this is let me explain.
I can change the way the dates are displayed but when I create the pivot table it uses the full date value giving me a pivot table column for every day not just every month as desired.
Can I actually convert the date data without manually going through and adding the month in a new data column?
Thanks in advance, Olivia
 

A:Solved: Excel reconfigure dates from mm/dd/yy to mm/yy for pivot

have a read here
http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/

you can group by month
 

Read other 3 answers
RELEVANCY SCORE 62.4

Hey Guys,

I am currently working with Excel 2007, but need this spreadsheet to work on older versions of Excel also.

I have a training tracker spreadsheet, containing details of what training has been completed by an employee and when.. some of this training is one off (machinery etc), but some of it is required to be repeated on a timescaled basis (6 months, annually, etc.).

The fields currently contain the date the employee had their training signed off.. how can I make conditional formatting work so that say, after 6 months, the field colour turns red to make it stand out...??

Thanks in advance... let me know if a copy of the speadsheet will help, I can remove all personal details and give you a copy to play with.

Cheers
 

A:Solved: Conditional Formatting Dates (Excel)

Hiya
If you set the selection with the details as in the attached picture it should work.Please let me know if it doesnt and if it does then please mark this thread as solved.
 

Read other 2 answers
RELEVANCY SCORE 62.4

I have a row of dates against a surname and first name in one workbook that I need to organise. For example:

Graham Mitchell 1/12/07 31/2/08 6/6/08
Sam Knowles 1/11/07 6/1/08 4/4/08

I then need to organise them in a different workbook as follows:

November December January February
Graham Mitchell 1/12/07 31/2/08
Sam Knowles 1/11/07 6/1/08

What formula would I use to look at a line of dates in a workbook and put information into the cell if the month & year are the same but leave it blank if it different??

Hope that I explained this okay. I am using Excel 2007 by the way.

Lisa92
 

A:Solved: Transferring dates from one workbook to another in Excel

Read other 7 answers
RELEVANCY SCORE 62.4

I am trying to merge data from an Excel S/sheet to a Word doc and this data includes dates which I need in the format of '28 September 2005'. I've changed the format in both Excel & Word and for some reason it still merges the dates as '9/28/05'. I've even gone to the extent of putting a character in front of the date so Excel doesn't recognise it as a date but surely I don't have to do this everytime? I have pages & pages of Word docs to go through and edit this extra character out of.

What am I doing wrong???
 

A:Solved: Merging dates from Excel to Word

I do beleive I just figured it out myself! LOL

Simple add one space before the first number of the date in the Excel spreadsheet. I thought this would also add the space to the Word doc but it doesn't! Problem solved.
 

Read other 1 answers
RELEVANCY SCORE 62.4

I have an MI system that outputs a lot of information into Excel, I then need to sort the information as follows:

One entry will have multiple start and end dates for qualifications

For A27 I need to look at all of the A27 entries (1, 2, 3 etc) and put in the cell the earliest date.

For A28 A31 I need to look at all the A28 and A31 entries and put in the latest date.

I have attached a sample spreadsheet that will hopefully clarify the above.

Thanks, Lisa.
 

A:Solved: EXCEL 2007 Problem with Dates

Read other 7 answers
RELEVANCY SCORE 62

Hi

I need help from one of you excel gurus out there.

I will see if I can explain clearly what I am trying to achieve.
I would like a macro that searches a worksheet for all debit transactions and credit transactions seperately for a given date and then sums them up. For example if i had two debit transactions on the 17th March, the macro finds the range for all transactions on the 17th and sums up those debits. Again the same for credits.
Ok, here is where it gets trickier, I need it to do it for all days from 1/07 to 30/06. So it's searching to see if there are any transactions for any of those dates and then sums them up.

I appreciate any assistance

Thanks
Strybes
 

A:Solved: Excel 2003 Help with sum range of dates found

Read other 11 answers
RELEVANCY SCORE 62

Hi All

Does anybody know if it is possible to insert rows between two dates so that the sequence follows.

Example:
I have the following 3 dates on 3 seperate lines.

1/02/2009
1/07/2009
1/07/2010

Is it possible to do a macro or write visual basic code to add rows so that it looks like this:

1/02/2009
1/03/2009
1/04/2009
1/05/2009
1/06/2009
1/07/2009
1/08/2009
1/09/2009
1/10/2009
1/11/2009
1/12/2009
1/01/2010
1/02/2010
1/03/2010
1/04/2010
1/05/2010
1/06/2010
1/07/2010

Thanks Strybes
 

A:Solved: Excel 2003 Insert Row between dates help needed

Read other 9 answers
RELEVANCY SCORE 62

Hi all,

I am currently using Excel 2003.
I have a column of dates which display the following format 17-Feb-2006. I would like to copy theses from one cell to another, but not in a date format, but as text. Problem is, if I copy into another cell and change the cell format to text, it diplays the cell valuation of 38765.
I also try the data>table to columns route and it does show the cell as text but in this format: '02/17/06'

I believe in excel 97 I could simply copy>paste special>text only, but this seems to have been removed from excel 2003.

I have also simply tried to insert an apostrophe at the beginning of the data in the cell, but this also goes on to display the following format; '02/17/06'

Does this make sense and if so, can anyone pleeeease help.

Many thanks
 

A:Solved: Excel 2003 - Copying dates as text

Read other 7 answers
RELEVANCY SCORE 62

Hey guys i need a formula that can change my 1000's of dates from regular mm/dd/yy into year and Quarter

so for example my date is 10/12/05

i need it to output 2005 Q4

thanks
 

A:Solved: Excel-Changing dates to year and Quarter

Assuming your dates are in column A, you can use this formula and drag it down all of the cells. Hope that helps.

=RIGHT(YEAR(A1),4)&" "&"Q"&ROUNDUP(MONTH(A1)/3,0)
 

Read other 3 answers
RELEVANCY SCORE 60.8

I'm sure this a basic one - sorry - but how can I quickly set up project management sheets with say 6 months of 'week commencing' dates across the top columns?

Thankyou from a chilly but sunny London!
 

A:Solved: Excel tip - quick way to insert dates for project management sheets

Read other 9 answers
RELEVANCY SCORE 59.2

Problem in Excel 2010 [but revives a Closed TSG thread for Excel 2007]

The "Personal Macro Workbook" is a file called Personal.XLSB
The Personal.XLSB file is in the correct location /XLSTART/
The file properties option "Opens with Excel" is selected.
The file contains a (freshly re-made) macro.
(It also contained the same steps macro before it was deleted
and re-recorded during troubleshooting for this problem.)

When Excel is opened, the Personal.XLSB DOES NOT OPEN WITH it.

Because the Personal Macro Workbook does not open,
- existing macros do not function,
- nor can new macros be recorded

Same not-opening result, whether opened by clicking on
- Excel, in the Programs list or
- a desktop shortcut to a specific Excel file

However, the Personal.XLSB file can be opened and the macro made functional
by manually opening that file where it is stored in /XLSTART/

An unexplained workaround was identified in the closed thread.

*rename the Personal.XLSB to Personal.XLSA

I find this workaround effective.
The Personal.XLSA file opens with Excel and the macros are available.
But the file seems to be grayed out when viewed with Explorer in /XLSTART/

However, I do not like using workarounds, as they can cause trouble later.
I find that the "open with" setting was changed when the extension was changed
The setting is now "Opens with: Windows Shell Common"

I would appreciate an explanation how to make Personal.XLSB open as it should.
... Read more

A:Solved: Personal.XLSB in Excel 2010 does not open on starting Excel

Read other 16 answers
RELEVANCY SCORE 59.2

I've searched for issues regarding non-working VBA code written for Excel 2010 and compatibility wit Excel 2011 for MAC.
I am currently using Addin (*.xlam) files that hold all the necessary code to process multiple files without needing to use the PERSONAL.XLSB and as eliminating the need to copy VBA code to every file that requires.
This works perfectly with all the Windows Office versions.
Today a colleague want's to run this same file on a MAC book with Office 2011.
The moment he opens the Addin het gets an error that a library cannot be found, understandable, but the Tools Reference in the VBA project is also greyed out so I cannot even try setting any reference.
Does anyone have any experience with this or is this something that needs a complete new approach when it regards Office on the MAC?

I hope somebody can help
 

A:Solved: Excel 2010 xlam file not working with Excel 2011 for MAC

Read other 6 answers
RELEVANCY SCORE 59.2

We recently migrated from Office 2003 to Office 2010.

Just to give some background, (and I dont' know if this is relevant to this error), the Office 2003 excel did not accurately show the correct file paths where files were linked. It would show something in the C drive.

The issue I am concerned about today is this: when some users close files in 2010 (files were created in they sometimes get the error below:

'"The name ABE2, either conflicts with a valid range reference or is invalid for Excel. This name has been replaced with _ABE2"

Can someone assist me with this? I need to stop it from appearing and I am not sure how.
 

A:Solved: Excel 2010 v Excel 2003 Error Valid Range

Read other 14 answers
RELEVANCY SCORE 58

Hi – I need some help.

I have a log that lists the start date and end date of my fleet of cars. I want to be able to tell how many cars we had in our fleet on any day (and then be able to average out the number of vehicles in a month)

What I have is two columns with the first containing the start date of the car and the second containing the termination date of the car.

Example

Code:

21/04/2006 20/04/2010
21/04/2006 20/04/2010
27/04/2006 26/04/2010
28/04/2006 27/04/2010
1/05/2006 28/04/2010
3/05/2006 29/04/2010
3/05/2006 30/04/2010
12/05/2006 11/05/2010
17/05/2006 1/05/2010

On another sheet I have dates starting 1/1/2003 incrementing by a day up to 2013. What I am looking for is a way to count how many times each of the dates falls between (or on) the date in each of the ranges.
EG in the example data above 5/5/2006 = 7, 6/5/2006 = 7, ..., 12/5/2006 = 8. (I hope! )

Note - I am using dates in the format DD/MM/YYYY

Hope you can help!
 

A:Solved: Counting number of times dates occure between range of dates

Read other 8 answers
RELEVANCY SCORE 57.2

Once again I'm reaching out to the TSG forums for help. I have a column (bid request date) and another column (bid approved date). In my pivot table I have a count of these by my team members. # of Bids Submitted and # of Bids Approved and I want to add another column into the pivot table, "Approval rate" which divides the # of Approved by # of submitted. Is there an easy way to do this in a pivot table?
 

Read other answers
RELEVANCY SCORE 56.8

Hi All,

So recently I've been having trouble with numerous Excel Spreadsheets on Excel 2013/2010

Issues have included excel freezing, taking a long time to open multiple (large) spreadsheets, copying and pasting taking 3 minutes or more.

Just to give some background; let me start by stating that the excel sheets in question are large in size with lots of links to external sources. I would also add that these spreadsheets where originally created on 2010 rather than 2013. My computer is also running on a corporate network for which i'am an admin.

However, the spec of the machine i'am using, in my opinion, should be more than capable of running these large sheets:

HP Z240
Xeon CPU 3.30GHz
16GB
250GB SSD
Win 7 Pro

To remedy these problems i have tried numerous troubleshooting including:
Increasing the RAM to 32GB
Turning off protected view via GP
Installing Excel 2010 alongside 2013
Installing 2010 x32
Ensuring all macro's are enabled via GP
Rebuilding the box entirely (software)

None of these have resolved the issues.

I've now found that i have exhausted my knowledge on this issue and so, I'am reaching out to see if any one has any suggestions with how to fix this.

Any help anyone can provide is greatly appreciated!

Read other answers
RELEVANCY SCORE 56.8

Hello.
 
I'm making a new referral form for the company I work for to be used by the council. I'm having problems with the active X boxes in Excel 2010.
 
I work on a remote server but I don't think this would be the issue. On sheet one the boxes work fine and as intended however on sheet two the boxes on the left side are only usable once and while on the right side they work as they're meant to.
 
I have no idea why it's doing this so I thought maybe someone here can help me out. I've tried roaming the web as much as possible but can't find a solution to my problem. I have attached a picture to show which boxes I mean.
 

A:Having problems with ActiveX box's in Excel 2010

No one able to help?
 
I really am stumbed on this and would like to get this form up and running before scrapping it and trying a new one.

Read other 2 answers
RELEVANCY SCORE 56.4

I have recently installed Microsoft Office 2010 on a brand new computer running on Windows 7.

When I save a word document the only way you can view it is by saving it in Word document 97-2003.
When saving a spreadsheet in Excel, and you try to open it, it displays as a Adobe document which cannot be opened.

Can someone explain if I have done something wrong, and how do I rectify this problem.

Many Thanks.
 

Read other answers
RELEVANCY SCORE 55.6

Morning,

I've been racking my brains about this one lately as we have more users switching over to Windows 7.

I'm convinced it's Excel causing memory problems, but I'm unsure.

When working with excel for a short time, we're unable to switch to any other window on the taskbar. It's as if Excel is taking priority over anything else that is open on the taskbar. For example, if we have Outlook, Internet Explorer & Excel open, when we click out of Excel to go into Outlook, nothing will happen, but it seems as if it's bringing the window up in the backgroud.

We can fix this by closing Excel and starting it up again, but alas, some more usage and it starts again.



Both Windows 7 and Office are right upto date.

Any ideas anyone? Your help is much appreciated.

A:Windows 7 + Excel 2010 causing taskbar problems

Hi,

Mmm, I have been using Outlook2010, Excel2010 and Windows 7 Ultimate x64 for a while now and never encountered this at all.

Are you able to nail it down any better? For example, do you get the same problems if Excel is NOT open? Is there anything in Group Policies that could be controlling this?

Have you tried using TaskManager or Resource monitor to see what is hogging the focus?

Regards,
Golden

Read other 4 answers
RELEVANCY SCORE 54

I have a column of calendar dates (A1:A100) that do not include Saturdays or Sundays. I have certain production times for various products that I must base off of these dates and am having trouble counting an amount of days that doesn't include any Saturdays or Sundays in my count. Example below.

10-02-2006 I need to add 18 production days to this date. Workdays only being Mondays-Fridays, I need the answer of the formula to equal 10-25-2006.

Can anyone assist?

Ron
 

A:Need Excel HELP with Dates

Read other 7 answers
RELEVANCY SCORE 54

Filename: SysInfo.exe
Full Path: c:\Users\JIMBO\AppData\Local\Microsoft\Windows\INetCache\IE\RBAGCWGH\SysInfo.exe

____________________________

____________________________
Developers
Not Available

Version
1.0.0.2

Identified
6/27/2016 at 5:17:31 PM

Last Used
Not Available

Startup Item
No
____________________________
Many Users
Thousands of users in the Norton Community have used this file.

Mature
This file was released 4 years 8 months ago.

Good
Norton has given this file a good rating.
____________________________
Source File:
sysinfo.exe

____________________________
File Thumbprint - SHA:
8243b4ea661b060fe8cf4babc11ab5f51eadd28a0c9d66303183e8eceace8234
File Thumbprint - MD5:
Not available
 

A:excel dates

What is your problem and/or question?
 

Read other 2 answers
RELEVANCY SCORE 54

I have a friend from california he has got office over here in europ but because of work he needs too change his dates in excel too american format how can i do this?

A:Excel dates

Hopefully this guide will help.

Read other 2 answers