#### calculate back dates

Hi

is there a way to figure out a date 9 months previous to a date?

eg. I have an install date of 12/02/10, is there a way or a formula that can
count back 9 months from that install date? (they need to be weekdays)

thanks very much
 0
Utf
3/23/2010 2:29:01 AM
excel.misc 78881 articles. 5 followers.

5 Replies
669 Views

Similar Articles

[PageSpeed] 38

Just use the Date function, as in:
=Date(year(a1),month(a1)-9,day(a1))

Regards,
Fred

"sonia" <sonia@discussions.microsoft.com> wrote in message
news:63DDCF12-C093-4532-B71C-2FB9112A47B8@microsoft.com...
> Hi
>
> is there a way to figure out a date 9 months previous to a date?
>
> eg. I have an install date of 12/02/10, is there a way or a formula that
> can
> count back 9 months from that install date? (they need to be weekdays)
>
> thanks very much

 0
Fred
3/23/2010 2:35:44 AM
ty this one

=IF(MONTH(A1)>=9,DATE(YEAR(A1),MONTH(A1)-9,DAY(A1)),DATE(YEAR(A1),MONTH(A1)-9,DAY(A1)))

please reply if the post is helpfull

regrads

elMedex

"sonia" wrote:

> Hi
>
> is there a way to figure out a date 9 months previous to a date?
>
> eg. I have an install date of 12/02/10, is there a way or a formula that can
> count back 9 months from that install date? (they need to be weekdays)
>
> thanks very much
 0
Utf
3/23/2010 2:50:01 AM
sorry this is the one is easier

=DATE(YEAR(A1),MONTH(A1)-9,DAY(A1))

the other was a test but is redundant

"Alejandro Medinilla "elMedex"" wrote:

> ty this one
>
> =IF(MONTH(A1)>=9,DATE(YEAR(A1),MONTH(A1)-9,DAY(A1)),DATE(YEAR(A1),MONTH(A1)-9,DAY(A1)))
>
> please reply if the post is helpfull
>
> regrads
>
> elMedex
>
> "sonia" wrote:
>
> > Hi
> >
> > is there a way to figure out a date 9 months previous to a date?
> >
> > eg. I have an install date of 12/02/10, is there a way or a formula that can
> > count back 9 months from that install date? (they need to be weekdays)
> >
> > thanks very much
 0
Utf
3/23/2010 2:53:02 AM
Hi

I'm getting a #VALUE! error, everytime I try it.
I put the formulas in the way explained, but I changed A1 to the cell that i
have the install date in, (as i would change the cell to all the different
install dates, there isn;'t just one install date, there are many.

ANy ideas on this problem??

"sonia" wrote:

> Hi
>
> is there a way to figure out a date 9 months previous to a date?
>
> eg. I have an install date of 12/02/10, is there a way or a formula that can
> count back 9 months from that install date? (they need to be weekdays)
>
> thanks very much
 0
Utf
3/23/2010 4:10:01 AM
Your most likely problem is your cell doesn't have a date in it, it has
text. You need to convert the text to a date.

To confirm this, simply enter 12/02/10 in your cell. See if that solves your
problem. If it does, then you will need to convert your other cells to
dates.

Regards,
Fred

"sonia" <sonia@discussions.microsoft.com> wrote in message
news:94082B8F-D7B4-43CC-A096-5952BB208B19@microsoft.com...
> Hi
>
> I'm getting a #VALUE! error, everytime I try it.
> I put the formulas in the way explained, but I changed A1 to the cell that
> i
> have the install date in, (as i would change the cell to all the different
> install dates, there isn;'t just one install date, there are many.
>
> ANy ideas on this problem??
>
> "sonia" wrote:
>
>> Hi
>>
>> is there a way to figure out a date 9 months previous to a date?
>>
>> eg. I have an install date of 12/02/10, is there a way or a formula that
>> can
>> count back 9 months from that install date? (they need to be weekdays)
>>
>> thanks very much

 0
Fred
3/23/2010 3:19:59 PM
 Reply:

Similar Artilces:

OWA with Static Ports Mapped on the Back-End EX2K3 Servers
HI, I recently configured my back-end Exchange 2003 server to use Static Ports using a registry change as specified in Microsoft Knowledge Base Article 270836. I reboot the server and everything seems ok. I can connect to the server via Outlook 2003, no problems, but when I try to connect to Exchange via my OWA server I get 'Server Unavailable'. This forces me to undo the registry change on the Exchange Server, and things are back to normal. Is this not working because I have not rebooted the OWA server after the back-end server was configured for static ports, does it need to ...

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

How to tell when a transaction has been rolled back?
I have a CLR stored procedure that uses a transaction to insert a row into a table. I need to tell somehow by looking at my sample data whether or not the rollback has actually happened. The only thing I have to go on is the HeadlineID int identity(1,1) not null column. Is this possible? 1. Run the stored procedure with sample data forcing it to succeed 2. Look at the data in the table itself. The current identity is set to 28. 3. Run the stored procedure again forcing it to fail. This is done by a unique key column [HeadlineTitle]. 4. Look at the table again to make sure this ru...

Cannot get emails back from Deleted Items folder
Outlook 2003 After deleting an email, I normally can move the email from the Deleted Items folder back to my Inbox. Or after deleting, I can Undo and it restores the email back to its original folder. Now, for some unknown reason, the Deleted Items folder will not let go of emails. I tried moving, clicking and dragging, and undoing, but they are stuck in the Deleted Items folder. All I can do to survive is copy them back. Any solution to this? how 'full' is the deleted folder and the folder you want to move them back into? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself ...

Balance of OT calculation
Hi Please help to set the formula for the calculation of "Balance of". Thanks 3 a b c d e f g h I 4 Apr 04 May 04 Jun 04 Jul 04 Aug 04 Sep 04 Oct 04 Nov 04 Dec 0 5Overtime: 1st 3 hrs 10.0 0.0 10.0 0.0 10.0 10.0 10.0 10.0 10.0 6 4th hrs 0.0 5.0 1.0 0.0 2.0 2.0 2.0 2.0 2.0 7Total claim 10.0 5.0 11.0 0.0 12.0 12.0 12.0 12.0 12.0 8Total set-off 3.0 11.0 1.0 17.0 1.0 1.0 1.0 1.0 1.0 9 10 B/F 7.0 1.0 11.0 (6.0) 5.0 16.0 27.0 38.0 49.0 11 12 Balance of: 13 April 7....

Pick up date for Charts from the Spreadsheet
I have a workbook with one data sheet and several charts which represent weekly activity. I want to be able to pick up the title of the charts from the spreadsheet, e.g. "Sales Activity for Week Ending April 7, 2006". This title information is contained in a cell in the spreadsheet. How do I get it to display at the top of each chart without having to manually type it in the title area under Chart Options? Wilfred: Here' a link to a post I have on dynamic titles that sounds like what you are looking for. http://processtrends.com/pg_chart_dynamic_chart_title.htm I ...

Date formatting Help
Macro - Help How can you select a cell with a Sunday's Date to give you th following Sunday's Date when execute -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27551 To get next Sunday's date, just add 7 to this Sunday. So something like: cell.value + 7 -- Regards, Fred Please reply to newsgroup, not e-mail "Db1712" <Db1712.1f88gn@excelforum-nospam.com> wrote in message ...

problems with document converted back to publisher 2000
I am trying to send a flyer that was created in publisher 2003 to a client - she requested that I convert it to Publisher 2000. I went in and saved it as a new document in the 2000 format, but when trying to email it as an attachment to her it stalls in my outbox and will not send. Any ideas? Lynette The file is probably too big. Try sending it thru yousendit.com. It's a free service. Warn her about the size. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Lyn11vw" <Lyn11vw@discussions.microsoft.com> ...

use front-end as a back-end temporarily
I need to do some maintenance on our Exchange back-end server. My thought was to change our front-end server to a back-end and simply move the mailboxes and public folders [small] to it so I can then take down the main back-end server for maintenance. Will this work OK? Is there a better solution that does not involve buying a new box? Is there a guide with step by step instructions? -- Sharingly yours... What maintanance do you need to do ? Easiest way would be to schedule downtime or is this not feasible ? Other option is if you are using Outlook 2003 cached mode is to have everyo...

I hit save instead of save as...is there any way to go back?
I need to retrieve the information from the page before I saved. I needed to hit save as and accidently hit save. Anyone???? You've kinda burned your own bridge. -- JoAnn Paules MVP Microsoft [Publisher] "Jr Bullet" <Jr Bullet@discussions.microsoft.com> wrote in message news:C9AD451B-209A-448F-935B-B2D1EC64A733@microsoft.com... >I need to retrieve the information from the page before I saved. I needed >to > hit save as and accidently hit save. Anyone???? On Thu, 10 Nov 2005 00:52:09 +0000, Jr Bullet wrote (in article <C9AD451B-209A-448F-935B-B2...

Dates in Charts
I have a chart that I have created based on data that lists dates and amounts. The dates are not consecutive however when I create the chart it shows all dates March 1, March 2, March 3 Even though I did not enter March 2 at all. How do I stop it from doing that? you cant. just like if you had numeric data 0, 3, 9, 15 and plotted it. the numbers in between are still shown on the axis. AGP "Erika" <Erika@discussions.microsoft.com> wrote in message news:4BC1E855-7741-44A3-9E5F-7E928A16AE15@microsoft.com... >I have a chart that I have created based on data that lists ...

How would you manage these dates?
I'm back, after posting just a couple of hours ago about "old" dates and getting great help. Now I know more about what my friend is trying to do, and I'm intrigued by the problem and am wondering how a real clever expert, like in this group, would handle it. She's archiving historical letters and using an Excel file. So she's got rows with the topic, who wrote it, who received it, the date, etc. Let's ignore the problem of "old" dates now and assume they're all after 1900. Here's the tricky part: she's not sure about some dates. Thi...

Need to automaticallu input date in form
This seems to be a very popular question posed on the forum, and I have read through and tried the most viable solutions given to others. I have three diferent forms that update a table with three different date fields. Although I have set the default value for all the date fields in the table to =Date()- 1, this only works on one of the forms, even though that field in each form is given same properties. The forms are used to update an inventory table containing part numbers, wip dates and amounts, packing dates and amounts, and shipping dates and amounts. I use the three different forms bec...

date doesn't get entered in cell
I give up, why is the "inputDate" variable not getting put in the "v5" cell? In "a4" cell the "f_FnameLname" variable is getting entered just fine. Also, is there a way to format the date so that in the dialog box the user will have to enter a date? tia, Public Sub SingleMonths() 'months macro rewritten 'used for a single patient sheet to be printed Dim inputDate As Date Dim i As Long inputDate = InputBox("Enter a date:", "Date", Date) 'requires date input from the user in a dialog box For i = 2 To Worksheets.Count...

#DIV/O! Errror with Absolute Number Calculation
Hi, I am calculating the following formula. =ABS(K41-J41)/ABS(K41) The result is #DIV/O! many times because K41 is 0 and the result is #DIV/O! I'd like the result to be "N/A" or something that I could put in between parenthesis. I'd be happy with any recommendations you have. Thanks!! Try this... =IF(K41="","N/A",ABS(K41-J41)/ABS(K41)) -- Biff Microsoft Excel MVP "Brent" <Brent@discussions.microsoft.com> wrote in message news:93A3C05A-89EC-4229-BA99-C23EF2E24AA8@microsoft.com... > Hi, > > I...

Highlight multiple cells (one column) that are less than today's date
Hello, I have a column of dates that needs to be compared current date and highlighted if the date is less that today's date. The dates were populated in a general non-specific format (below) and vary in length. My plan was to create a macro, that uses conditional formatting to say; if the cell date is less than today's date highlight it yellow. I am not finding this to be an easy task! Anyhow, after creating the conditional formatting; I apply it the column by using a past special, then lastly apply a format to the entire column of dates as DATE "*3/14/2001". What hap...

How to synchronize email delivery date
Hi, is there any possibility how to transfer email send/delivery date from outlook to MS CRM when I track the message in CRM? When I check history of any client, all activities has their start/end dates but email dates are empty. I have examined the data in MS CRM and found that the actual delivery/send date is not included in the record. The only date attached to this record is the date/time of performing the track action. Any suggestions? Thank You Best regards George Hi George, there is a Hotfix from Microsoft wich closes this problem. The Hotfix take the recieved date and writ...

Difference between dates as xxYears yy Months zz days
I have a number of pairs of dates where I wish to show the difference between each pair in the format of e.g. 3 years 7 months 9 days. I can work out the yearsand months but don't know how to then get the number of remaining days allowing for differences in number of days in a month or year. Does anyone have a set of formulae or a macro that will produce what I want? Thanks Rich 80105 subtract one date from the other and format the result cell using custom format of yy "years" mm "months" dd "days" hope this help -- tony ----------------------------...

Payment Calculator not working correctly
Recently I tried to program an Excel spreadsheet to calculate a mortgage Payment using the =PMT(Rate/12,Term,Loan Amount)*-1 formula that I have used on many other spreadsheets. These older spreadsheets all work properly, but they were all written on another computer and imported into the one I am now using. The formula “=PMT(10/12, 360,100000)*-1” should give me a payment of \$877.57, but it gives me \$83,333.33 instead! Is their a flaw in the newer Excel versions, or a patch I don’t have? Any help would be appreciated. -- SwimBob No doubt you meant =PMT(0.1/12,360,100000)*-1 (10% ...

Change date format in Excel footer
I'm trying to change the format in my Excel footer to dd-mmm-yy. I have changed the short date in my regional settings from dd/MM/yy to dd-MMM-yy. In the Regional settings it shows the format I want. My Excel footer has changed from dd/MM/yy to dd-MM-yy, but still doesn't give a three-letter month. I am using Windows Vista Home Basic and MS Office 2007 -- Tibs You will have to go for a VBA solution. Set the security level to low/medium in (Tools|Macro|Security). From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the...

Custom date field and Fill
I have a customer that is using a date/time fill that isn't calculating the right time. It can be duplicated on a new spreadsheet. Start with "A1" and enter in "1/29/2004 7:30:50 AM" and format the column with this custom type: m/d/yyyy h:mm:ss AM/PM. the fill series should decrease by 20 seconds every cell. So I manually put in the first 5 cells as follows: 1/29/2004 7:30:50 AM 1/29/2004 7:30:30 AM 1/29/2004 7:30:10 AM 1/29/2004 7:29:50 AM 1/29/2004 7:29:30 AM Highlight the series and drag it out past A110. In cell A103 I have lost a second. the value should ...

VBA322.dll out of date in XP
Hi, In XP, Excel97 complains 'VBA322.dll is out of date.' Does someone know how to remedy this? Thanks, yt ...

Auto Date Population #2
Hello Everyone - Here's what it is: I use an Excel-based form to order product. What want is to set up a chosen cell to show the date of the followin Tuesday, regardless of when I fill out the form (for instance, if were to fill out the form today - 7/20/04 - I would want the chose cell to read the date for the following Tuesday - 7/27/04. Under different circumstances, I would simply enter a formula to sho the date X days from today, but I don't always place the order on th same day. Can this be done? Thankx, C -- Message posted from http://www.ExcelForum.com Hi for your...

Back Up Problem
I am using Windows XP SP3 I perform my Documents and Settings backup daily and usually it takes about 4 hours to backup my 64 GB info. But since two days ago, it took more than 21 hours to do the backup. I haven't changed any settings in my XP or installed any new hardwares/softwares. Can anyone advise me what is the problem that caused this slowness? Any comments/suggestions are much appreciated. Thank you. Need a lot more info. What do you backup to? What program do you use? Documents and settings really 64 GB? XP home? pro? Your system? "silkunicorn" <s...

Why is the actual calculation different from actual sample
This is my Formula =(a1-a2) a1 = 37.5 a2 = 10 response should be -27.5 But response comes to 28 and under format the sample says 27.5 why ? You either have a typo or don't understand arithmetic. 37.5 - 10 is 27.5 10 - 37.5 is -27.5 You will see 28 or -28 if formatted to no DP Gord Dibben MS Excel MVP On Sat, 1 May 2010 09:36:01 -0700, Joe <Joe@discussions.microsoft.com> wrote: >This is my Formula > > =(a1-a2) > >a1 = 37.5 >a2 = 10 > >response should be -27.5 > >But response comes ...