Formulas to (a) pull month, year and (b) sum within date range?
I have a list of dates in column A, and numbers down cols B-E. Off to
the right, I need a table that lists the month/year and the totals for
each of the columns. It needs to be a separate table, rather than a
pivot table, so it can be copied-and-pasted by others.
What I _think_ I need is:
-- a formula in H2:H10 (maybe more) that will look down the full range
of dates (call it A2:A100) and put one month/year per row
>> H2 = 02/09
H3 = 03/09
H4 = 04/09
-- a formula in I2 that will sum all entries in B2:B100 with the month
and year of H2
I think I could get the secon...Calculating Time 05-21-10
I'm setting up a timesheet but when I enter the format to + or - if the
'time' is negative the figures are not showing - is there a way around this?
Only by turning on the 1904 date system, which I wouldn't suggest :)
"Fie M-C" <FieMC@discussions.microsoft.com> wrote in message
> I'm setting up a timesheet but when I enter the format to + or - if the
> 'time' is negative the figures are not showi...More Date problems
I have a macro that deletes returned records older than 7 days. It first sorts the data into returned date order then checks to see if the first cell is empty (no records returned) and if so, re-sorts the data into the original order (a different date) and quits. If the first cell contains a date less than 7 days old ie date >today()-7, again it re-sorts and quits, otherwise it deletes the data older than 7 days, re-sorts the remaining data and quits. At least that what is supposed to happen
The line to check the age of the date in the macro below doesn't work. G6 contains a control ...Okay, it's time to call in the troops
I've nearly split my head open trying to figure out this issue, so I've
decided to call upon the experts for some assistance with what could be a
simple database structure problem, or Access quirk I'm not familiar with.
I've created a database which holds respondent answers to a survey aimed at
university donor satisfaction. The first piece I'm trying to extract data
from and report on surrounds the type of gifts a donor has participated in
(annuities, trusts, etc.).
I have drawn CRAT, CRUT, CGA, CLAT, PIF as the gift types from my master
table, and created a query for...Shortcut Ctrl-X not working on multiple cells
In Excel 97, on some spreadsheets I can't use Ctrl-X to cut multiple
cells at once. Other ways to cut work, but Ctrl-X is my preferred
method. On other spreadsheets this does work. Has anyone ever seen
What happens when you try?
Cuts wrong area?
Gord Dibben MS Excel MVP
On 21 Sep 2006 09:57:16 -0700, "CDM" <email@example.com> wrote:
>In Excel 97, on some spreadsheets I can't use Ctrl-X to cut multiple
>cells at once. Other ways to cut work, but Ctrl-X is my preferred
>method. On other spreadsheets this does w...How do I create an alert of expiration date in Excel?
I created a spreadsheet to track documents. The last column is for
expiration dates of documents. How can I set up an alert when the expiration
date is approaching?
Use conditional formatting in the cell. For example, with the date in A12,
Formula is =TODAY()+7>=$A$12
as the condition.
Then choose a pattern.
> I created a spreadsheet to track documents. The last column is for
> expiration dates of documents. How can I set up an alert when the expiration
> date is approaching?
...How to set default zoom scale for IE8?
Does anyone have any suggestions on how to set default zoom scale for IE8?
I am using XP with SP3, the default zoom scale is 100%, and I would like to
change it into 135% as default.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Click the "Change zoom level" arrow at the bottom right corner in IE8, click
"Custom" and set it to 135%.
Click Tools, Internet Options, Advanced. Uncheck "Reset zoom level for new
windows and tabs".
Ramesh Srinivasan, Microsoft MVP [Windows Desktop ...How do I change the default reminder time on custom follow up dat.
I want to change the default reminder time when I select "Custom" as my
follow up flag. Right now it defaults to 4pm. I know where the reminder
time setting is in Tools/Options, but that's not changing the reminder time
for Custom follow up flags.
Change the Quick Click Flag to anything except Today and it will default to
the start of your work day.
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com/
Outlook Tip...User Defined Function vs. Named formula
I have used Named Formulas and am now learning about User Defined
Functions. One thing I found with named formulas is that the naming
conventions around global/local selections. Reading about UDFs makes
me think they might be a better option for a lot of my work.
Any insight as to When to use one over the other will be appreciated.
IMO they are different beasts.
A named formula would be used to shorten formulae by including a name rather
than the sub-formula (especially where it is repeated), and to improve
readability/maintainability, as the name would/should be meanin...how to automate min and max on axis based on calculation
While automating some chart making with code, I am trying to automate the
span of time reflected on the x-axis. Auto doesn't work because I need the
beginning to be based on an October of a given year. Similarly I have
requirements for the max.
The appropriate min and max are calculated elsewhere, but I am not sure how
to code it to accept the value from a cell.
Any help would be much appreciated.
This has to be done programmatically. For a ready-made solution see
www...Difference between % Gain and Total Return for All Dates?
Could someone tell me what the difference is between % Gain and Total Return
for all dates? Which tends to be a more accurate picture of my portfolio
...How do I change the date format?
If anyone could help, I would be appreciative... I have 800 rows of data
where the date is currently formatted:
1. YYYYMMDD How do I change this to MM/DD/YYYY?
2. Telephone number is currently formatted as 7607236300. How do I change
this to 760-723-6300?
Thank you for helping me.
Select the column with the dates
Data|text to columns (xl2003 menus)
Choose Fixed widths, but don't include any separator lines.
Choose Date (ymd order)
and finish up.
Change the number format (format|cells|number tab) to the format you want
#2. Select the range t...stopping the date from changing in Excel (and Google Spreadsheets)
Wehn I enter "October 10, 2006" into my excel spreadsheet, it always changes
it to "10-10" or something like that. Where can I set my excel spreadsheet to
display the full date?
Also, does anyone have any experience with Google spreadsheets? Any good?
Go to Format, Cells, Number, Date and pick the date format that works for you.
> Wehn I enter "October 10, 2006" into my excel spreadsheet, it always changes
> it to "10-10" or something like that. Where can I set my excel spreadsheet to
> display the...complete a line in the middle of a graph to meet both X and Y axes
I have created a line chart with limited data and now want to execute that
line in order that they intersect both x and y axes, on the same path..
How can i perform this in Microsoft Excel
...Defining a cell to name file
Is it possible to define a cell in which would become the name of the excel
"TimlmiT" <TimlmiT@discussions.microsoft.com> wrote in message
> Is it possible to define a cell in which would become the name of the
I've got the cells formatted with "Custom" and m:ss.
I'm using the formula:
and it gives #DIV/0
Is there any way to keep the times in minutes:seconds format and still
get the average?
F10:F15 probably does not house true time values. You can check whethe
this is so with...
> I've got the cells formatted with "Custom" and m:ss.
> I'm using the formula:
> and it gives #DIV/0
> Is there any way to keep the times in minutes:seconds format and still
> get the average
-...Creating a time line
I am interested in creating a timeline of events. Is there
a way to do this using Office 2000 software?
look at micrrosoft project instead
"melanie" <firstname.lastname@example.org> wrote in message
> I am interested in creating a timeline of events. Is there
> a way to do this using Office 2000 software?
...XY Scatter Chart Not Showing X Axis
I'm trying to create a quadrant analysis. I have the four quadrants as
stacked columns and the real data as a XY Scatter. The x axis for the XY
Scatter is not showing. The real data series are marked as the secondary
axis. Does anyone know how to make the X axis appear?
Usually Excel gives you both secondary axes when you add an XY series to a
column chart. Go to Chart menu > Options > Axes, and check the box for the
secondary X axis. If you're using Excel 2007, it's found on the middle of
the three charting tabs, under Axis.
Jon Peltier, Microsoft ...Several files opening at the same time
I have a shortcut on my Quick Launch bar to a file called Index.xls wherein other hyperlinks take me to a zillion other places as
a means of organisation of my computer data.
For a couple of months I've been finding that whenever I click Index.xls another file called PERSONAL.XLS opens as well (or in
fact momentarily before). I don't know why this is happening. As far as I know I did not change anything that might cause this
I located PERSONAL.XLS and changed the filename to PERSONAL.XL1 to see if that might prevent it from opening, but now I have
PERSONAL.XL1, PERSONA...Date Modified sort issue.
The issue is that when I sort (right-click --> arrange icons by --> Modified
or click on the Date Modified header), it sorts by Date Modified, but
categorizes by the Name (Folders, Shortcuts, Files. etc.).
I was wondering if anyone knew of a way to sort an entire folder (either
ascending or decending) by Date Modified.
Thank you, WHEELS
-------- Original-Nachricht --------
> Good afternoon.
> The issue is that when I sort (right-click --> arrange icons by --> Modified
> or click on the Date Modified header), it sorts by Da...Count if using multiple date criteria
I am working on a spreadsheet that records inventory. I need to create a
formula that counts if any items were completed late by a person . The
information regarding the person is recorded in one of two columns (M and N).
The date completed is recorded in column O. The due date is column G. I
also need to record this for each month received. The received date is
column A. So for example, I need to know how many items received in November
2009 and assigned to J. Smith were completed after the due date.
Thanks for any help you can give!
Something like this:
...Date format for any LCID
It's not possible to retrieve a friendly named date description
including the weekday name from the OS, is it?
I am looking for a function that would return for example
Monday, 05.01.2010 (in English->LCID 1033)
Montag, 01.05.2010 (in German->LCID 1031)
I would like localize my invoices, and I don't want to rely on my own
Boris P. wrote:
> It's not possible to retrieve a friendly named date description
> including the weekday name from the OS, is it?
> I am looking for a function that would return for example
> M...Date time problem
I have a date and one cell and a time in 24 other cells at 1 hour increments.
how do i combine the date cell with the time cells
in a1 =9/26/05
in b1 = 08:00
results in c1 would = 9/26/05 8:00 AM
Microsoft MVP - Excel
"FSt1" <FSt1@discussions.microsoft.com> wrote in message
> hi all,
> I have a date and one cell and a time in 24 other cells at 1 hour
> how do i combine the date cell with the time cells
> in a...Extracting times
I have a spreadsheet which has time ranges entered in the format "13:00 -
17:00" in a single cell.
I wish to have these times automatically transferred to separate cells in
another worksheet, eg "13:00" and "17:00"
Use Data->Text to Columns ... delimited by - to split into two columns....
Then either paste or link to the other worksheet.
Pl. click ''''Yes'''' if this was helpful...
> I have a spreadsheet which has time ranges entered in the format "...New Date on each new page
I would like to print a page for each day for the upcoming year. 365 pages.
I want to print all of them now.
On each page I need the date for that day. What should I use for this? Pivot
Enter the first day in A1 before you run the macro
If it is working correct change PrintPreview to PrintOut
This example will not print the sunday's
Dim yr As Integer
yr = Year(Range("a1").Value)
Do While Year(Range("a1").Value) = yr
If Application.WorksheetFunction. _