calculating dates prior to a given date

Hi. I am trying create a spreadsheet that will have dates listed in one 
column and have another column list the date 14 days prior to the first 
column's dates.  I'm hoping to then organize the second column ascending from 
the closest  to the furthest date, compared to the current date.  If anyone 
has any help they could provide it would be much appreciated. Thank You.
0
Utf
12/15/2009 7:59:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
590 Views

Similar Articles

[PageSpeed] 49

If you have a date in A1 and you want another date in B1 which is 14
days earlier, then put this in B1:

=3DA1-14

Copy this formula down, then fix the values by selecting all those
cells with the formula in, clicking on <copy>, then right-click and
Paste Special | Values (check) | OK then <Esc>.

Insert a new column B, then sort column C, then delete column B.

Hope this helps.

Pete

On Dec 15, 7:59=A0pm, Eric <E...@discussions.microsoft.com> wrote:
> Hi. I am trying create a spreadsheet that will have dates listed in one
> column and have another column list the date 14 days prior to the first
> column's dates. =A0I'm hoping to then organize the second column ascendin=
g from
> the closest =A0to the furthest date, compared to the current date. =A0If =
anyone
> has any help they could provide it would be much appreciated. Thank You.

0
Pete_UK
12/15/2009 8:28:45 PM
Reply:

Similar Artilces:

dates #5
how to change text dates (03082007) to mm/dd/yyyy (03/08/2007 Data>Text to Columns>Next>Next>Column Data Format>Date. MDY or DMY Gord Dibben MS Excel MVP On Thu, 8 Mar 2007 14:05:23 -0800, duine <duine@discussions.microsoft.com> wrote: >how to change text dates (03082007) to mm/dd/yyyy (03/08/2007 ...

Calculated fields in the Sub Grand Totals
How come these don't alays work? ...

Overtime Calculation based on multiple hourly pay codes.
The system presently is just not designed to calculate overtime based on multiple hourly pay codes. If you enter 20 hours in one hourly paycode and 25 hours worked in another, these would all need to be entered for the same hourly pay code in order for 5 overtime hours to auto-calculate. The system should have the ability to check all the hourly paycodes for a given pay-period and then automatically calculate overtime if need be. Thanks Girish ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this s...

Date created and date last modified
In previous version of Word, you could check the "date created" and "date last modified" in the document by drilling down through properties, and then summary. This information is not available in Word 2007 documents. Is there any way to discover these attributes of a file in 2007? Click the Office button, click Prepare, and then click Document Properties. In the pane that displays, click Document Properties, and finally click Advanced Properties. You'll find the data you are looking for on the Summary tab. -- Stefan Blom Microsoft Word MVP ...

Outlook received date incorrect
3 out of 5 users running Outlook 2000 are getting all their email with a received date of October 4, 2001. Once they open the message the sent time is correct and if they sent me an email outside of the company the date is correct on the received date. Internally the received date it fine. 2 users are unaffected. I have checked for virus, Outlook calendar time, regional time on the pc Internet Headers under view/options, also ran the office update and it did not help....any ideas??? Thanks in advance...SMF ...

calculations not coming out right... ASAP!
i have a form that does not multiply correctly. i enter Quantity, UnitPrice, check Expendable (if needed and this adds 10% to the UnitPrice). then the UnitPrice and the 10% are added together.. everything is fine. I then multiple the UnitPrice (which goes to NetAmount) by the Quantity which is then put in TotalAmount but is off by roughly 2 cents. It only happends when i check the Expendable box. Here are my formulas.... Private Sub expen_AfterUpdate() expen = expen / 100 End Sub Private Sub Expendable_AfterUpdate() If Me!Expendable = True Then Me!Netamount = Nz(Me!UnitPrice, 0) * [ex...

Looking for LATEST DATE
I am using Access 2003 I have a machine maintenance Db. This is a one to many db. One machine number and many operations and maintenance dates. I need to create a field that looks for the last maintenance date and adds the frequency in days for each operation to come up with the next maintenance date that is due. Ex: Machine #100 had maintenance done on Jan 3, 2006, July 5, 2006, and again Jan 4, 2007. (180 day intervals). Each date would be a different record for the same machine #100. How can I have Access find the latest date (jan 4, 2007) add 180 days and tell me that the nex...

check register by date, not alpha
I did something, and it changed to alpha listings. I previously had it by date, and prefer this way. How do I get back? Thanks, Randy In microsoft.public.money, randy wrote: >I did something, and it changed to alpha listings. >I previously had it by date, and prefer this way. >How do I get back? At the top of the register, View:AllTransactionsCoveringAllDates,SortedByDate(Increasing) ...

Exchange SP2 release date?
Does anyone know a release date for SP2 other then "late 2005" Thanks On Mon, 1 Aug 2005 13:25:20 -0400, "Peretz Stern" <peretzstern@optonline.net> wrote: >Does anyone know a release date for SP2 other then "late 2005" > >Thanks > Yes, 2005 - Autumn/Winter (Q4) It'll be ready when it's ready. Are you another one of the folk who are hovering around the 16GB limit and don't need the features of Enterprise apart from the additional storage? Yup. "Mark Arnold [MVP]" <mark@mvps.org> wrote in message news:8tnse154...

Filter records by date in subform by date entered in mainform.
Hello, I've spent 3 days trying to understand what I'm doing wrong.....Here is the summary of my problem. I have a form named 'frm_EfficiencyStats' and a subform within the form named 'qry_Total hours subform' (the subform is based on a query). The form contains a text box (for date) which is bound to a table 'tbl_EfficiencyStats'. I would like to be able to enter a date in the text box on my form, and display the records in the subform based on the date I entered in the text box. The subform will only pull records from my table based on the date. Here are th...

date and time stamp on command
Hi. I have the formula in which if I update a cell in a row, column A will time stamp the change. Private Sub Worksheet_Change(ByVal Target As Range) Range("A" & Target.Row) = Now() End Sub However, I would like to have a time stamp in column A, whenever the same row (column B) is updated with a "Y". I am keeping a daily log and want to be able to control the date and time stamped by placing a Y in column B. I want to know what time a Y was placed on the row. In short terms, Y = date/time stamp, no Y, no date/time stamp. Example below: A ...

Why does Mac change PC dates?
I'm running Excel 2004 for the Mac (version 11.1). When someone sends me a PC-created workbook with dates, I read the dates fine. But if I copy and paste them into a new workbook, each date advances by one (ex: 10/16 to 10/17). Anyone know what's going on? The date is probably being advanced by four years, because the Mac uses the 1904 date system, and Windows uses a 1900 date system. There's information in the following MSKB article: XL: The 1900 Date System vs. the 1904 Date System http://support.microsoft.com/kb/180162 walter4e wrote: > I'm running Excel 20...

date format problem #12
Hi, i have a problem. my userform has a text box that enters the date when used. my code is this: UserForm1.TxtQuoteDay.Text = (Date, "Long Date") the problem is, when the data is entered to my sheet, the date comes out like this: 09 May 2005// Everything is fine except for the //. Can this be removed or am i doing something wrong? the cell format is date 09 May 2005. Help appreciated. Regards, Nigel Did you mean something like: UserForm1.TxtQuoteDay.Text = Format(Date, "Long Date") And what does "when the data is entered to my sheet" mean? If it means...

Date woes using VBA
I have a table with a field for dates, which contains four rows. I am attempting to use the first date to populate a field in a different table, using an update query written in SQL. The syntax is: rs.ActiveConnection = DR rs.Open "AttDatesT" With rs rs.MoveFirst datDate = .Fields("DateId") MsgBox strDate strSQL1 = "UPDATE [Students-T] SET [Students-T].Date1 = " DoCmd.RunSQL strSQL1 & datDate End With rs.Close The message box displays the date correctly, eg 10/02...

Exporting Outlook Mail Dates to Excel
I want to export the information from a mailbox in Outlook to an Excel file. I can do that, it gives me a spreadsheet with my choice of: - Subject - Body - To (Name, Address, Type) - From (Name, Address, Type) - CC (Name, Address, Type) - BCC (Name, Address, Type) - Billing Information - Categories - Importance - Mileage - Sensitivity ...isn't there something conspicuous missing from this list? Namely, date/time? What day the email was received is exactly what I want to be processing in Excel. This mailbox receives automated emails and I want to trend activity over time. Can someone...

Delay Calculation of A Cell
I have a cell with the following calculation =IF(AND(C62-C56>0,C70-C66>0),"Yes - Loan Should be Approved",IF(AND(C62-C56<0,C70-C66<0),"No - Speak to your Advisor.","Possible - Depending on Detailed Application, speak to your Banker")) Now it works ok but I want to stop it calculating until the "user" requests an answer. The second part is how do i tell the spreadsheet to remove all data in "unprotected cells"? The idea the user opens the spreadsheet completes a series of cells then selects answer to display the...

Select on current date
Hi all, having a table in SQL Server 2000 with a DateTime field and some values like: ReferenceDate 2009-05-26 22:30:00.000 2009-05-27 12:30:00.000 2009-05-27 22:10:00.000 2009-05-30 22:40:00.000 2009-05-31 14:55:00.000 how can I write a Select that gets records that have DateReference = getdate()? So records where this field fall inside actual date. Thanks in advance. Luigi Hi SELECT * FROM tbl WHERE ReferenceDate>=GETDATE() AND ReferenceDate<DATEADD(d,1,GETDATE()) Note if you have huge table to check for ranges ,created clustered index on Referen...

calculating time with IF statement
I have the following: A1 is formated as time = 7:30 AM A2 is formated as time = 6:00 PM A3 is formated as time and contains =sum(A2 - A1) and gives 10:30 That part I've got down. Now, I want to subtract 30 minutes so I added: A4 is formated as time and contains =sum(A3-30/1440) and gives 10:00 Worked, thanks to those that have posted these questions. Now the problem: I only want to subtract the 30 minutes if cell A3 is greater than 7:00 How do I write that IF statement? Thanks for the help, Mark You could have used: =a2-a1 =a3-30/1400 =sum() doesn't really add anything to...

Formula/calculation help for medical charting
Hi, I am trying to develop an anesthesia record chart for the vet clinic I work at. I have A8 through A15 labeled for time, temperature, heart rate, etc. The other columns (B8-B15, C8-C15, etc) I have set aside for later entries in the above categories as we take values every 5 min. or so. What I would like to do is have a formula that would show numerical values in red if they are above or below the normal value range (in black). For instance, a dog's normal temperature can range from 99.5 degrees F to 102.5 degrees F. If it is 98 degrees or 103 degrees I want these values to s...

Calculation Speed
For a particularly large excel workbook, the recalulation time is quite long. Any suggestions to reduce calculation times? The following are some things I'm thinking about. Please feel free to comment on their plusses/minuses or add to the list. 1. Use Manual Recalc 2. Break workbook into smaller workbooks 3. Get more CPU 4. Allocate more CPU to Excel. 5. more? Thank you. Mark Hi Mark, I have a web page on the subject. Slow Response, Memory Problems, and Speeding up Excel http://www.mvps.org/dmcritchie/excel/slowresp.htm Charles Williams has a website on the subject, one...

If Calculation
Hello to all, I�m a new member at the forum. A question that i have, I want to calculate the following: Suppose that in cell A1 is number 1, cell B1 is number 2 and cell C1 i number 100. On cell A2 is number 1, on cell B2 is number 3 and on cel C2 is 100. Now i want the calcutation to cell D1 to returns if values on the cell A1=1 and B1=2 then C1*5%. For the second row if A2=1 and B2=3 the C2*6%. As it is a big file i want to make one calculation so when someon makes copy-paste to do the calculation automatically. I hope that you can understand me. Thanks a lot -- Adiorthoto ----------...

date in the form of yyyymmdd
other than entering 'yyyy-mm-dd' as date, are there ways to enter dates "yyymmdd" if I enter, for example, 20070101 and change the cell format to yyyymmdd and the cell becomes lots of #. Look here: http://xldynamic.com/source/xld.QDEDownload.html or here: http://www.cpearson.com/excel/DateTimeEntry.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "kang" <kang@hao.com> wrote in message news:eExl1$WCIHA.1184@TK2MSFTNGP04.phx.gbl... | other than entering 'yyyy-mm-dd' as date, | are there ways to enter dates "yyymmdd" | | if I enter...

Date Calculation 02-20-08
A co-worker gave me a database (Paradox) that he needs converted to Access. The database is an overtime equalization database that allows supervisors to keep track of voluntary worked overtime. Here is my dilemma: The work schedule which is used is a rotating schedule. The schedule is called a 6-2 (Work 6 and off 2). You start out with a Monday/Tuesday as you days off. The following week you have Tuesday/Wednesday. Then you have Wednesday/Thursday off. Then Thursday/Friday off. When you get to the weekend you have Friday/Saturday/Sunday. The following week you have Saturday/Sun...

In Excel, Get todays date in a cell
In Excel, how do i get the spreadsheet to automatically update a cell entry to today's date? (i.e., if i open the spreadsheet on 10/5/05, a cell is programmed to show 10/5/05; if i open it on 10/6/05, the cell shows 10/6/05, etc.). =TODAY() "tbw" wrote: > In Excel, how do i get the spreadsheet to automatically update a cell entry > to today's date? (i.e., if i open the spreadsheet on 10/5/05, a cell is > programmed to show 10/5/05; if i open it on 10/6/05, the cell shows 10/6/05, > etc.). =Today() Or use =Now() for date and time and format for date THES...

How do I count the number of records within a date range
How do I count the number of records within a date range. eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 = 2 =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") Format cell as General "Mike" wrote: > How do I count the number of records within a date range. > eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 > = 2 try =sumproduct((year(daterng)=2003)*1) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mike" <Mike@dis...