Calculating Dates, PlEASE HELP! THANKS!

Ok, If I want to calculate the difference of a date with the following date 
in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value 
in B1 of 35.  How do I do this?  Thanks!  

In Excell the formula looks like this:

=IF(+A1-A2>0,A1-A2,0)

0
Utf
1/27/2010 3:37:02 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
581 Views

Similar Articles

[PageSpeed] 57

Is this an Access question?  

If so, then post you table and field names with datatype, sample data, and 
what you want the results to look like.

-- 
Build a little, test a little.


"kritter0021" wrote:

> Ok, If I want to calculate the difference of a date with the following date 
> in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value 
> in B1 of 35.  How do I do this?  Thanks!  
> 
> In Excell the formula looks like this:
> 
> =IF(+A1-A2>0,A1-A2,0)
> 
0
Utf
1/27/2010 3:47:02 PM
well Access doesn't have cells, it has fields and records, but i think
i know what you mean.

you want to use the DateDiff function.

DateDiff ( interval, date1, date2)


so if you want the number of Days, set the interval to "d"


more info here:

http://www.techonthenet.com/access/functions/date/datediff.php
0
ghetto_banjo
1/27/2010 3:48:07 PM
kritter0021,

Oops, I think you are treating an Access table like and Excel spreadsheet. 
No can do!  For totaling in Access we have queries.  Each *row* is a record, 
each *field* (not cell) is a *part* of that record, not a field all by 
itself.

If your data is set up like an Excel spreadsheet in Access move it back to 
Excel and do your calculation.  If in fact you want to use Access then your 
tables will most likely have to be redesigned.  However, we are going to 
need to know more about what you are doing.

-- 
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors 
II

http://www.regina-whipp.com/index_files/TipList.htm

"kritter0021" <kritter0021@discussions.microsoft.com> wrote in message 
news:03EBB1D4-626F-4666-82BA-5796272B2520@microsoft.com...
Ok, If I want to calculate the difference of a date with the following date
in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value
in B1 of 35.  How do I do this?  Thanks!

In Excell the formula looks like this:

=IF(+A1-A2>0,A1-A2,0)


0
Gina
1/27/2010 4:22:57 PM
Yes.

Here is the data:
Product	Date	Dif Date
A	1/23/2009	40 
A	3/4/2009	65 
A	5/8/2009	0 
B	3/2/2009	28 
B	3/30/2009	0 

Text         Date           Number

"KARL DEWEY" wrote:

> Is this an Access question?  
> 
> If so, then post you table and field names with datatype, sample data, and 
> what you want the results to look like.
> 
> -- 
> Build a little, test a little.
> 
> 
> "kritter0021" wrote:
> 
> > Ok, If I want to calculate the difference of a date with the following date 
> > in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value 
> > in B1 of 35.  How do I do this?  Thanks!  
> > 
> > In Excell the formula looks like this:
> > 
> > =IF(+A1-A2>0,A1-A2,0)
> > 
0
Utf
1/27/2010 4:46:01 PM
Yes.

Here is the data:
Product	Date	Dif Date
A	1/23/2009	40 
A	3/4/2009	65 
A	5/8/2009	0 
B	3/2/2009	28 
B	3/30/2009	0 

Text         Date           Number

I am wanting to calculate the Diff Date and when it moves to a new product 
display the average of of the product, so instead of displaying 0 show the 
average of A of 52.5.  Not sure if this is possible.  Real easy in excell. 

"ghetto_banjo" wrote:

> 
> well Access doesn't have cells, it has fields and records, but i think
> i know what you mean.
> 
> you want to use the DateDiff function.
> 
> DateDiff ( interval, date1, date2)
> 
> 
> so if you want the number of Days, set the interval to "d"
> 
> 
> more info here:
> 
> http://www.techonthenet.com/access/functions/date/datediff.php
> .
> 
0
Utf
1/27/2010 4:52:08 PM
Use these three queries --

   kritter0021
SELECT YourTable.Product, YourTable.ProdDate, (SELECT COUNT(*) FROM 
YourTable AS [XX] WHERE  [XX].Product = YourTable.Product AND [XX].ProdDate 
<= YourTable.ProdDate) AS RANK
FROM YourTable
ORDER BY YourTable.Product, YourTable.ProdDate;

   kritter0021_X
SELECT kritter0021.Product, kritter0021.ProdDate, kritter0021.RANK
FROM kritter0021 
UNION ALL SELECT YourTable.Product, NULL AS ProdDate, Max([RANK]) +1 AS RANK1
FROM YourTable INNER JOIN kritter0021 ON  YourTable.Product = 
kritter0021.Product
GROUP BY  YourTable.Product;

SELECT kritter0021_X.Product, kritter0021_X.ProdDate, 
IIf([kritter0021_X_1].[ProdDate] Is 
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate])) 
AS Expr1
FROM kritter0021_X INNER JOIN kritter0021_X AS kritter0021_X_1 ON 
kritter0021_X.Product = kritter0021_X_1.Product
WHERE (((kritter0021_X_1.RANK)=[kritter0021_X].[RANK]+1))
ORDER BY kritter0021_X.Product, kritter0021_X.ProdDate, 
IIf([kritter0021_X_1].[ProdDate] Is 
Null,0,DateDiff("d",[kritter0021_X].[ProdDate],[kritter0021_X_1].[ProdDate]));

-- 
Build a little, test a little.


"kritter0021" wrote:

> Yes.
> 
> Here is the data:
> Product	Date	Dif Date
> A	1/23/2009	40 
> A	3/4/2009	65 
> A	5/8/2009	0 
> B	3/2/2009	28 
> B	3/30/2009	0 
> 
> Text         Date           Number
> 
> "KARL DEWEY" wrote:
> 
> > Is this an Access question?  
> > 
> > If so, then post you table and field names with datatype, sample data, and 
> > what you want the results to look like.
> > 
> > -- 
> > Build a little, test a little.
> > 
> > 
> > "kritter0021" wrote:
> > 
> > > Ok, If I want to calculate the difference of a date with the following date 
> > > in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value 
> > > in B1 of 35.  How do I do this?  Thanks!  
> > > 
> > > In Excell the formula looks like this:
> > > 
> > > =IF(+A1-A2>0,A1-A2,0)
> > > 
0
Utf
1/28/2010 4:02:54 AM
Reply:

Similar Artilces:

How chart time scale x-axis with another date serie
Hello all. I have this problem, I will need to create this chart that I don't know how to. Serie 1 is below which needs to be time scaled Date Count 1/1/2006 12 1/2/2006 3 1/3/2006 5 2/1/2006 18 2/3/2006 4 2/18/2006 9 4/1/2006 18 7/4/2006 38 Serie 2 - needs to plot the three dates in serie 1's time scaled X-Axis with symbols. 1/31/2006 2/18/2006 4/2/2006 I will look something like you have a line of count on a time-scaled chart, with 3 dates point in the x-axis. Please ...

Error message needed if incorrect Date Of Birth (DOB) entered
Hi, In cell D6 I have a DOB entered....in cell E6 I have a datedif function that has 3 conditional formatting set as: 1 - If D6 is blank, E6 is blank. 2 - If DOB in D6 is between 18 - 24 then E6 shows age and cell is yellow. 3 - If DOB in D6 is 25 or over then E6 shows age and cell is blue. What I need is when a DOB is entered in D6 under 16 years old, then D6 returns an error message -" You have Entered an Age under 16 Years - Retry!! ". I have tried Data Validation, but cant get a message from tha as I dont know what to set as the date validation. I do need a...

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

last 4 wk average using calculated pivotitem
Hi, I am trying to create a pivot table/chart from data that is listed b week. So my table has "week" as the row field and "total X" as th column field. I want the chart to display the total for each week as bar (the easy part) and also to graph the average of the last fou weeks as a line on top of the bar graph. I have tried to figure ou how to add a calculated pivot item but I can't seem to get it to wor right. So for each week there would be a second almost subtotal lik entry that calculates the average for the previous four weeks (if ther aren't four previo...

Excel Regional Date Format Options
A client of ours in NZ is complaining that date format options for English (New Zealand) have changed from older versions of excel (they are using 2003) Some of their spreadsheets have dates formatted as dd-mmm-yy, mmm-yy and dddd,dd,mmm but these options do not exist anymore. Is there anyway to add options to this list without using the custom format option? Thanks, Jesse I just compared the Excel 97 and Excel 2003 built-in date formats and they are mostly unchanged. 2003 has a few more but I don't think there were any subtractions. The formats dd-mmm-yy and mmm-yy are righ...

mscvr71.dll help
How do I make my VS.net 2003 software not depend on msvcr71.dll? Thanks. Use static linking. I don't know where to set the option in VS7, but it used to be under Code Generation where you selected the desired C runtime library to use. In VS6 we had a choice between a dynamically-linked runtime and a statically-linked runtime. I've not had to make this choice under VS7 so I'm not sure where, in their overly-clever and completely gratuitous reimplementation of the user interface, this has been hidden. joe On Sat, 21 Feb 2004 22:24:56 GMT, anonymous@coolgroups.com wrote: &g...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

24 Hour Calculations
I was wondering if anyone has a solution for calculating (in 24hr Format): Total Shift Hours per employee when start of shift is: 6/13/05 20:00 ending 6/14/05 04:00. AND Once shift totals are calculated in a column, subtotal both employee weekly hours and company man - hours per shift. If you have entered both the date and time, as you show, in a single cell, then you can simply subtract the two and format as time to get the hours worked. As far as the subtotals are concerned, SUMIF will work for the totals by employee. If the starting times for each shift are identical for all employee...

Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them. Here's what I have so far: From To Length of Service 01/09/2003 31/01/2010 6y 4m 30d 01/06/2000 30/11/2002 2y 5m 29d Total of Service: ?????????? I've used the following formula to calculate the total days worked: =DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Where can I get a template that will calculate profloss after buy.
I need a template that will calculate all the possible variables in a share trade. The rate on buying, every day entry (7 days),Profit/loss on holding, prof/loss on sale etc. ...

Date/Time Displayed as 07/tt/jjjj
Hi, I'm new with MS CRM and just testing it. I have a prob with all date/time fields like described in the subject of this post. Timezone is set for all users Problem occurs in all date/time fields in the web application Anyone has a clue? Regards Jan As Administrator, go to Home > Settings > there is an area in there (can't remember what its called) that enable you to change default date/time format "JanScheider" <JanScheider@discussions.microsoft.com> wrote in message news:F1F22171-AEDC-4222-A8CE-3E333B0576CA@microsoft.com... > Hi, > > I'...

Thank you very much Mary!
Just a short note of thanks to Mary Sauer who did her absolute best to help me with my greeting card printing problem. This site and her assistance were extremely useful. Thank you again, very much! Petra -- Dog is my co-pilot Hi Petra, I am sorry I haven't returned the edited card. I'm getting there. Thanks for your comments, I appreciate them. I hope it is warming up in Canada. We had snow this morning!!! -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Petra" <Petra@discussions.microsoft.com> wrote in mes...

SmartList Restrictions help
I built a SmartList that is based on the Year-to-Date Transaction Open file, and has the Account Master linked to it. I want to restrict it to accounts that begin with 36, 38, or 2504. I tried adding a restriction that says "Account Number:Account_Segment_Pool1 begins with 36 OR 38 OR 2504", but I got no results when I did it that way. I also set up 3 separate restrictions, but that didn't work either. Is this possible? I can't find much information about how to write restrictions in SL Builder. ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

help plz
my account has been inactive how to i reacctivate it? What account and what does this have to do with Outlook? "heather" <tracyliepke@yahoo.com> wrote in message news:066001c36c53$bb68d180$a501280a@phx.gbl... > my account has been inactive how to i reacctivate it? ...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

How do I subscribe, please?
Hi! I know this is a lame question..... but how do I subscribe to this list, please? I use AOL as my ISP and yahoo as my e mail. Please reply to me at mhendersona2@yahoo.com. Then I'll subscribe and ask a *real* question....... Thanks! Mary Hi Mary, (posted with email copy) This is a public newsgroup, and in my opinion the word subscribe is a misnomer. The fact that you can read the newsgroup is what is meant by subscribed. There is no permission to be granted from an administrator in order to post, you posted and your posting will remain posted. You will see ...

Need help to choose loyalty program integrated with website
Hello. My name is Alex and I am working for franchise company using RMS system. We are looking for loyalty program integrated with web store. We have 12 franchisee stores using RMS and they are all conneted to our HQ system in main office. We want customers to earn point for each sales and redeem their points only at our website. (not on off-line store) Can anybody recommend best solution for our plan? Thank you. ...

Help And Advive For A Novice
HELP AND ADVICE FOR A NOVICE This Is Not A Question About Using Excell But How To Download E -books I Have Purchased 2 E-books (a)f1 Excell And (b) Financial Statements. Encountered Difficulty In Downloading Because The Instructions Were Not Very Clear For Novice And Message Flagged Up "zip File Corrupted" I Have Utilised My Permitted Downloads. Therfore I Have Not In A Position To Use The E-books -- Joe Williams ------------------------------------------------------------------------ Joe Williams's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23...

formula help #42
What formula would I use to search down a column find a name and report the number in the next column, this would be multiple times, the numbers to be added together. The added number reported then to be multiplied by another number and then to be subtracted from another fixed number in a specific cell. Thanks in advance Jason You can sum the corresponding cells matched without having a dedicated column of numbers. =SUMIF(A1:A100,"Name",B1:B100) =(SUMIF(A1:A100,"Name",B1:B100)*AnotherNumber)-SpecificCell HTH, Paul -- "Boenerge" <Boenerge@discussions...

HELP! Balloon Payments in Debt Reduction
I have had this problem for so long it is getting really frustrating. When I put a debt into the debt reduction plan, it always puts a balloon payment on my first payment. Then, if I don't make that payment, it just builds and builds and builds with each subsequent payment I do make. How do I get rid of this? I removed the debt from my reduction plan the first time and that cleared it. But, the moment I started paying on the debt, it returned. Now, it's to the point that I can't get rid of it at all. When I remove the debt from the plan, it will do the same thing w...

Comparing 4 dates and getting 1 headache
Hi everyone, I have a problem hopefully some one can help me out with. I have the following 4 dates… Beginning Date of Service Ending Date of Service Eligible Beginning Date of Service Eligible Ending Date of Service I need a formula that will compare the Beginning Date of Service to the Eligible Beginning Date of Service, if the Eligible date is later it will take that if is earlier then it will take the Beginning Date of Service and compare it to the Ending Date of Service if the Eligible Ending Date of Service is later then the Ending Date of Service or the Ending Date of Serv...