a running sum (by date) in a query?
Is this possible? I have dates (some of which are the same) and I
would like to use them to create a running sum of another column in my
query. Is this possible?
On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote:
> Is this possible? I have dates (some of which are the same) and I
> would like to use them to create a running sum of another column in my
> query. Is this possible?
Sorry, not only do i need to create a running sum by date but it also
has to be PER sales person. So even though all the sales people are in
one table and I am querying the table different ways, I need to m...Text date to date
Is there a function that will convert text dates (month, year)
You can use:
And then format the cell the way you like M/D/YYYY
(and even convert the formula to values if you want to delete the original data)
> Is there a function that will convert text dates (month, year)
> JULY, 2009
> AUGUST, 2009
> SEPTEMBER, 2009
> to this:
...Chart data scrunches to left when adding X axis date series
I have a combination chart that has stacked bars and a line series. I have a
Y axis with a secondary axis. I'm attempting to add a X axis with a date
series. As soon as I do that, Excel starts thinking for me and all my data
scrunches to the left side of the chart. I don't' have any idea how to fix
...date subtraction difficulty
I'm trying to subtract dates, to calculate time duration. I'm
subtracting column A from column B into column C. Column C is
formatted 'm "mos", dd "days"' and columns A and B are formatted
'mm/dd/yy/@' Unfortunately each of the durations is 1 month too
long. That is, if the real duration is 4 days, column C will
display '1 mos, 4 days'
Any clue what I'm doing here?
On Tue, 18 Jul 2006 11:58:10 -0500, Peter <firstname.lastname@example.org>
>I'm trying to subtract dates, to calculate time durati...Remove time from date upon export to csv
I cannot remove the time from the date when exporting invoice data to a csv
I have followed previous advice and created a query based on my tblInvoices
table, in which I have formatted the InvDate field in the Design Grid as
Date: Format([tblInvoices.InvDate], "dd/mm/yyyy") (UK format)
In the Query, this is fine - just the date, no time shown.
I saved an export spec in the Advanced bit of the File/Export wizard.
But in the resulting csv, there are six zeroes representing the time!
However if I do a manual export by following exactly the same process bu...Excel template for the date of Easter
Does anyone have access to an Excel template for finding the date of Easter
in any given year in the past or in the future?
> Does anyone have access to an Excel template for finding the date of
> Easter in any given year in the past or in the future?
There was a competition in Germany to find easter date in excel, I believe
however it won't work for Mac date system (1904), this formula by Gerhard
Somitsch w...Dates as text
using a formula, I am combining a column heading and the date in that row, so
that the cell will read "Column Heading Date", but the date is showing as a
number rather than a date. I want the cell to contain both txt (column
heading), and a date. How do I format it to do so?
assuming your date is in cell A1:
="heading text "&DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1)
hope to help,
> using a formula, I am combining a column heading and the date in that row, so
> that the ce...Date Function?
I want a criteria that will return a list of records within a specified
month. The "Month" and the "Datepart" Function don't seem to be working for
me. Any advice?
"Caravatis" <Caravatis@discussions.microsoft.com> wrote in message
>I want a criteria that will return a list of records within a specified
> month. The "Month" and the "Datepart" Function don't seem to be working
> me. Any advice?
WHERE (((Month([Dat...Subtracting Cells formatted with Date AND Time to get hours?
Thanks in advance for the help. Is it possible to subtract 2 cell
that are both formatted as mm/dd/yyyy h:mm to just have it subtrac
the times to get a total # of hours between the 2. For example, A
is 1/1/05 13:00 and B1 is 1/2/05 14:30. I'd like C1 to give me th
total of 25:30. This forum is always a great place for help. Thank
again in advance for your help.
=B1-A1 and format as [hh]:mm
"mcr1 - ExcelForums.com" <email@example.com> wrote in
> Thanks in advan...Date and Time 05-14-10
I have cells in a worksheet that are formatted as date but still show as date
and time. How do I pull out the time.
04/07/2009 1:00:00 AM
I have tried the =left() and also =right () formulas with no luck.
Date and time are just numbers underneath the formatting.
04/07/2009 1:00:00 AM is 39910.04167
Format the cell to General to see that number.
Use Data>Text to Columns and delimit by decimal point.
In third step choose the right hand column and "Skip"
Re-format the cell.
Gord Dibben MS Excel MVP
On Fri, 14 May 2010 14:30:0...Compare two dates
Hi,. I would like to compare two dates in the same row and return a true or
How do I do this?
Could you please elaborate your query. Do you mean?
With dates in A1 and B1
=IF(A1=B1,"dates are same","dates are not the same")
If this post helps click Yes
> Hi,. I would like to compare two dates in the same row and return a true or
> false statement.
> How do I do this?
"Dave" <Dave@discussions.microsoft.com> wrote in...Form and default dates
Related to my question below.... I'm setting up a form to filter results of a
query, I'm trying to set default dates (at the least so when you open the
form nothing will be filtered by default, at the best I'd like to be able to
open the form directly without it prompting me to enter values for the
filters and just show everything by default)
At any rate I have 2 issues now.
First no matter what I put in Default Value for my date entry boxes on my
filtering form, 12/30/1899 shows up in both.
Second as soon as I click in one of the boxes to enter a date it changes ...Select Date based on date value
Is there a formula where I could select a date based on a condition?
here is a scenario of what I want the formula to do:
I have 3 dates:
start date,end date, and invoice date, let's assume that the start dat
is 9/7/2005, end date is 10/6/2005 and invoice date is 10/8/2005. no
even though the invoice was sent on 10/8/2005, I would like to identif
this month as september because the bulk of the month fell on september
I guess the rule would be(per example above):
per example above:
if starting month >= 09/20 then select 10/2005
else month < 9/20 then select 9/2005
so if the 20th...subtracting units of time
I am trying to keep track of my hours at work, as I need to make up a day I
spend at hospital during the course a month. I have put together a sheet
(sort of a cut-down version of my department's timesheet), with day, month,
in and out. What I want to do is subtract 30 minutes from each standard
work day, so I end up with 8 (or however many hours worked) minus my half
hour lunch break.
Any help would be greatly appreciated.
Auckland, new Zealand
Stick 0:30 in a cell and name it lunch_break.
Then use = A1-lunch_break, where A1 contains your times be...date format #9
Is there any way to add automatically "rd" or "st" or "th" etc after
I have a date in A1 as "30-01-2001" and I need to show in B1 as
Hope someone has an idea
Message posted from http://www.ExcelForum.com/
One Way. Use:
Using SQL 2005
Is there a way to query a date range with only one date provided? The date
field is a timestamp with mm/dd/yyyy mm:ss. User provides date from a
dropdown list on a form - example 12/01/2009. We want to provide all records
that fall in the month of Dec 2009.
WHERE [DATE] >= '12/01/2009' ???
How to get just the dates for that month?
Your help is greatly appreciated
Message posted via SQLMonster.com
You can calculate range from first of the mont...Date sent items are 2 years earlier to the system date
When I sent mail using Outlook 2000 on a Windows 2000
svpack 2, the sent date is 2002. The receiver also sees
this date. The system date of my PC is correct, as is the
system date of the server running Exchange server 5.5
Other users do not have this problem. Please help!
On sheet A, I have column: A1(product name) A2(quantity).
When I copy a row with the data in above columns to sheet 2, it must
automatically deduct 1(one) from A2.
Can you please help?
Message posted from http://www.ExcelForum.com/
this would require VBA (using event procedures) Do you really want to
go this way. Note: excel is IMHO not well suited for maintaining an
> On sheet A, I have column: A1(product name) A2(quantity).
> When I copy a row with the data in above columns to sheet 2, it must
> auto...Sorting by Date (With a Twist)
A coworker gave me a list of data. One of the columns of this data was a
date field formated custom in mm/dd/yyyy format. The problem was that
when my coworker sorted the date field it was sorting by the day.
No problem I thought, so I created a new column typed in =year(e2)
which was the cell of the first date. To my suprise the data came back
as 1905. The date in E2 was 5/16/2003. If I did =month(e2), I got
January (in mmmm) format.
B. Baumgartner's Profile: http://www.excelf...Subtracting figures
If i was to sell a product that i had bought and entered
into Microsoft Money how do i tell money to minus the
balance of what i sold it for? For example, i buy a phone
for $50 and this goes under the Phone expenses category.
Later on i decide to sell it and i manage to get $20 for
it. How do i tell money to minus the $20 from the
original $50 i paid?
In microsoft.public.money, Stan wrote:
>If i was to sell a product that i had bought and entered
>into Microsoft Money how do i tell money to minus the
>balance of what i sold it for? For example, i buy a phone
>for ...Making a graph with easily changed dates
My boss asked me to make a graph on one of his spreadsheets more user
friendly...so that you could put a start and end date in two ajacent cells
and the graph will automatically change to show the time between those dates.
I didn't think it was going to be too hard but now i'm completely
stumped...I've got the dates on the X-axis and 3 other lines that come from
data on another sheet...and i can't even get the X-axis to easily adjust to
two changeable dates (it will just show those two days rather than all the
days inbetween).....if anyone has any suggestions i'd gre...deleting wrong dates
Hello... i have an access 2007 database. I have a date field ([Sales-Date
Install #9]) with incorrect data in it.
I would like to update those fields to Null value.
here's the sql i'm using:
update STR_CRM_1_Contacts set
STR_CRM_1_Contacts.[Sales-Date Install #9] = Null
WHERE not isdate(STR_CRM_1_Contacts.[Sales-Date Install #9]);
unfortunately this doesn't work.
I have data in this field, like: 11/30/122 or 11/30/134...
I would like to get rid of those incorrect values.
so what's wrong with my query???
Any help will be appreciated.
Those are valid...Activity modified date
Is it possible to show the activity modified date on the
Accounts view? I am able to add the modified date to
that view - but that just changes when the actual account
is updated - not when an acitity is added/changed.
the modified ondate is tied specifically to an object such as account etc
Also the views will only show data from each object ie you could not have a
view showing fields from both an activity and account on the same view. You
may have to create a custom view using the SDK to achieve this
"Kelly Hunt" <firstname.lastname@example.org> wrote in message
I been trying this for hours....
Is there a way to subtract / add TIME on a timesheet?
I have an excel document that the user puts in time in and time out (format
Then they have to go to the ttl hours field and do the subtraction/addition
Is there a way to do this?
Yes, use =1time+2time or =sum(1time,2time) or =sum(timerange) for adding
and use minus for subtracting =end_time-start_time
"George Czarnonycz" <email@example.com> wrote in message
> Hell...Formula needed to calculate difference between Two dates and times
I want to know the formula for calculating the below mentioned problem
col A B C
1 10.8.08 11 AM 11.8.08 3PM 2days ( i need formula here)
We have a small inn and i need to calculate the duration of stay by a guest.
Checkout time is 24 Hrs. The Column C should display 1 if the duration is
less than 24 Hrs and it should display 2 if its more than 24 and less than 48
and so on. PLEASE HELP
Put this in C1 =ROUNDUP(B1-A1,0) and copy down. Format column C as Number
with no decimal place. Col A & B should have dates with time.