#### Date Calculations

```I have to create a formula in excel that takes a date (4-1-2010) and
subtracts another date from it (2-1-2008) and gives me the remainder of
months.  Any ideas?
```
 0
Utf
4/8/2010 8:25:01 PM
excel.misc 78881 articles. 5 followers.

3 Replies
765 Views

Similar Articles

[PageSpeed] 34

```How do you define a month difference? 30 days? Calendar months? What
about months with different numbers of days. E.g., how many months
between 28-Feb and 31-March. 1? 1+3/30? You need to define how the
months should be calculated. At its simplest, just subtract one date
from the other and divide by 30. That will give one of several

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Thu, 8 Apr 2010 13:25:01 -0700, Erika
<Erika@discussions.microsoft.com> wrote:

>I have to create a formula in excel that takes a date (4-1-2010) and
>subtracts another date from it (2-1-2008) and gives me the remainder of
>months.  Any ideas?
```
 0
Chip
4/8/2010 8:43:31 PM
```See if this is accurate enough for you

A1 = 4/1/2010
B1 = 2/1/2008

C1 = A1 - B1
D1 = ROUND(C1/30,0)
or
E1 = ROUND((A1-B1)/30,0)

D1 and E1 give the same answer, just one step for E1.

Jeff

"Erika" wrote:

> I have to create a formula in excel that takes a date (4-1-2010) and
> subtracts another date from it (2-1-2008) and gives me the remainder of
> months.  Any ideas?
```
 0
Utf
4/8/2010 8:44:01 PM
```On 8 avr, 22:25, Erika <Er...@discussions.microsoft.com> wrote:
> I have to create a formula in excel that takes a date (4-1-2010) and
> subtracts another date from it (2-1-2008) and gives me the remainder of
> months. =A0Any ideas?

Hello,

You can have a look at the Excel DAYS360  function which returns the
number of days between 2 days based on a 360 days year. This formula
takes 3 parameters : start_date, end_date and  a boolean which allows
to choose between 2 calculations methods : the american method or the
european method, take a look at the Excel Help on this function for
more details on this option.

Hope this helps

new1@[no/spam]realce.net
```
 0
new1
4/8/2010 8:58:16 PM

Similar Artilces:

Calculate Difference in Rows
How do I calculate the difference between two numbers in different rows? "Glenna" wrote:subed going to jail > How do I calculate the difference between two numbers in different rows? "Glenna" <Glenna@discussions.microsoft.com> wrote in message news:B551C7E2-392A-4E7B-BAA8-5E3189FBEF8F@microsoft.com... > How do I calculate the difference between two numbers in different rows? In a report, this may help: http://support.microsoft.com/kb/290136 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp...

calculate a date, 10 weeks after a set date & return a specific da
I would like to calculate a date, 10 weeks after a set date and return the date of that tenth week monday? Is this possible. On Sat, 20 Mar 2010 15:49:01 -0700, Art3277 <Art3277@discussions.microsoft.com> wrote: >I would like to calculate a date, 10 weeks after a set date and return the >date of that tenth week monday? Is this possible. If your set date is in cell A1, try the following formula: =A1+70-WEEKDAY(A1,3) Hope this helps / Lars-�ke See if this does what you want... A1 = some date B1 formula: =A1+70-WEEKDAY(A1+70,3) Format as Date (if n...

Date formatting problem.....
Hi All, I use the following code to create a string containing the current date. Dim sDate As String sDate = Format(Date\$, "mmm d, yyyy") This code works fine here in the US, but a user in the UK is reporting that it's giving incorrect results. For example, on Nov. 5th, 2009, this code creates the string 'May 11, 2009' on the UK machine. Obviously, the code is confusing the month (11) and the day (5) and transposing them. I've played with this a bit, but haven't found a solution. Suggestions? Thanks, John Nobody wrote: > "John...

Date #3
I have the following formula in a cell: =Sheet2!A4 and i want the next cell in that row to disply: =Sheet2!B4 How would i do this? Thanks One way =OFFSET(Sheet2!\$A\$4,,ROW(1:1)-1) -- Regards, Peo Sjoblom "Edgar" <anonymous@discussions.microsoft.com> wrote in message news:01db01c3a2f5\$8462a390\$a101280a@phx.gbl... > I have the following formula in a cell: > > =Sheet2!A4 > > and i want the next cell in that row to disply: > > =Sheet2!B4 > > How would i do this? > > Thanks If the "next" cell is to the right, simply copy the ...

Date Format Conversion?
I have a tab delimited text file with a column of dates. I copied and pasted a selection of them below as a reference, there are in reality over 35,000 rows of these dates. These text dates must be converted to the following date format and saved back into a text file as such: mm/dd/yyyy That includes leading zeroes in months and dates. E.g. 01/01/1999 To complicate matters, I need to explain the process to others. Does anyone even know where I should begin? THANK YOU, - Chris +++++++++++++++++++++++++++ 9031989 8131990 9201989 1271990 10291989 1081990 3041990 10051989 4191990 10...

Date Format #2
I have copied and pasted a word table into excel which was a pain in itself. The date format in word was Thu 21st June 2007. But the table would not sort into date order so I had to remove the "Thu" part. I then had to remove the paragraph sign as it was throwing extra cells in excel when I pasted it. Eventually the date went into excel but now I cannot format the date. When I look at the format 21st June 2007 it has defaulted to General under "format cell". When I change the format to say custome date it does not make a difference, the only way it shacges is if I man...

Calculating Work Hours
I've seen some posts using Workdays function to find elapsed time from date/time A1 to date/time B1; I'm trying to utilize this, but I have some data that actually begins after prescribed business hours and/or ends before... the formula I'm using doesn't like those scenarios... is there something that I can do to allow it to continue calculating JUST business hours, despite start/stop time anomolies? Brent, Visit Chip Pearson's excellent website: http://www.cpearson.com/excel/DateTimeWS.htm HTH, Bernie MS Excel MVP "Brent" <BrentEdwi...

Date of Last Sale
Is there a 'date of last sale' field in GP? We have used this information previously to determine slow moving stock. We are currently @ GP 10.0. Thanks There is not such a date in GP but you could use a simple query to pull that data from the SOP30300 (Sales Transactions Amounts History) table via the Actual Ship Date field. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your GPtip42today at www.gp2themax.blogspot.com Thanks, I am trying to create a crystal report, but the information I was seeing from that report did not c...

Total dates e!
Column A is Names! I would like to have Dates Missed in column B and the total number of all these days in a cell in Column C. Can you assist? Name DateMissed Totalnumberofdays missed =COUNT(B1:B1000) -- Regards, Peo Sjoblom "Gazabou" <anonymous@discussions.microsoft.com> wrote in message news:dd1d01c43ba2\$a9cfdfd0\$a501280a@phx.gbl... > Column A is Names! I would like to have Dates Missed in > column B and the total number of all these days in a cell > in Column C. Can you assist? > Name DateMissed Totalnumberofdays missed IN A1 type Names In B1 type Date...

I do a calculation in a cell, but it calcs as zero!
I am doing a calculation (Cell f7/22) - \$300,000.00 divided by 22 the result shows as Zero! Perhaps the \$300K isn't being seen as a number. Try "Data isn't recognized..." http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "G4" <G4@discussions.microsoft.com> wrote in message news:F6E95F42-3A33-4691-89D3-5374DCC9A0F3@microsoft.com... >I am doing a calculation (Cell f7/22) - \$300,000.00 divided by 22 > the result shows as Zero! ...

Date
I have a table with a start date column. I have created a query where I include the start date from my table, I have created an expression to add 24 months to the start date but what I would like to also have is either the specific date or the closest month of May. for example Start date is July 1, 2008 if I add 24 months it will show July 1, 2010 What I would like to see is May 2010 as this the month that we identify individuals that need to start a secondary process. Very new to access and not that great with formulas -- Thank You Sassy By 'closest' do you mean t...

Birth date Change??
Can I change 19840822 to read August 22, 1984 ?? Not just one time, but a whole column of 1000 names and birth dates. Hi try the following - select your cells - goto 'Data - Text to columns' - in the wizard choose 'Date' as filed type and select the correct date format - finish the wizard Prior to this make a backup of your data :-) -- Regards Frank Kabel Frankfurt, Germany Sillysamiam wrote: > Can I change 19840822 to read August 22, 1984 ?? > Not just one time, but a whole column of 1000 names and birth dates. Hi Sillysamiam! You can use: =DATE(A1/1...

How do I enter a date command in a header that displays only the current year and not the complete date? So the value in the header is 2010 or is it 1/1/2010 formatted to show only the year? If it is only the year then what date were you hoping for. What formula did you want to use against the date? -- HTH... Jim Thomlinson "Doug" wrote: > How do I enter a date command in a header that displays only the current year > and not the complete date? Jim, I want to insert only the year, but I want it to update with each new year. I use the same spreadsh...

Capturing Date
I have tried writing a macro that would let me capture the date from my computer and place it in cell D2. The captured day will be moved intact at the end of the day Is there a command that will let me do that? Thanks for the help in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200609/1 Hi, Why do you need a macro for this if Excel has a formula like this? In Cell D2, put: =TODAY() "Larry Fish via OfficeKB.com" wrote: > I have tried writing a macro that would let me capture the date from my > computer and place it in...

Calculated Field
I understand that calculated fields should be used in forms/queries, not in tables. My scenario is this. Table 1 Model Serial Total Flight Hrs. Table 2 Flight Model Serial FlightHrs etc.... Form FlightNo Date Model Serial Hours CumulativeFlightHrs (using dlookup for TotalFlightHrs + Hours from form) I want to always be able to know the cumulative flight hours and have it always associated with the model for exporting, etc. Is this ok, or is it better to not have the field in my table 1? -- Teach me to fish! Thanks for the help. Pax...

Workflow with prior dates
I need to create a workflow that when an appointment is created that it creates 2 tasks 1 day before the appointment date and 2 tasks 1 day after the appointment date - any suggestions on how this can be done? Thanks, Jeanne This is not possible in CRM 3.0. In CRM 4.0, you can use a Dynamic Expression. You can set the Due Date of the tasks to be 1 Day before the Appointment. "Jeanne" wrote: > I need to create a workflow that when an appointment is created that it > creates 2 tasks 1 day before the appointment date and 2 tasks 1 day after the > appointment date - any...

hide zero values in date formatted cells
Cannot find way to hide the value in cells that have a formula (in Date format). Example: User will enter a date in cell A1. Cell A2 and below will be a sum(A1+1) and copied down. The entire column is in a date format. But we want it all blank until user enter date in A1. One way: You can't do this with formatting, since, with no entry in A1, =A1+1 (you don't need the wasteful SUM() wrapper) will = 1, not zero. Instead, change your formulae to A2: =IF(A1="","",A1+1) and copy down. In article <0e1301c38774\$2303bef0\$a001280a@phx.gbl>, ...

Need help with XSLT for DATES in DataSet for XMLSS
I am using an XSLT file to convert data in a DataSet to XMLSS format for opening in Excel. Excel doesn't like the way Dates in the DataSet are being ouput. It appears that a timezone offset is being appended to the datetime; e.g., instead of "2003-08-26T00:00:00.0000000" I am getting "2003-08-26T00:00:00.0000000-07:00" in the output. If I could just get rid of the trailing "-07:00" in the output, I would be all set. I'm guessing this must be easy to do in XSLT, but I am far from an XSLT expert... Thanks for any advice! XML ERROR in Table REASON: Bad Value...

Excel 2003 Non-continuous dates
Hi, all, I've been using Excel for about 10 years. I'm using Excel 2003 on Win XP for the first time. I'm used to differences when going from one release to the next. I've run into a real problem with the way this version of Excel handles non-continuous dates in charts. It keep inserting blank entries for dates not in my source data. A simple example would be for the week starting Monday Dec 22, 2003. If there are values for sales on Mon through Wed, and Fri but not for Thursday, which was Christmas, I want Thursday to not exist in my Column Chart. Excel "...

convert date to week, starting at financial year
I have query that counts how many bookings were made on a certain date, but I need to total it by each week (for example 5 on monday and 2 on wednesday, week 1 total = 7) I have worked out that you use: Format([dteDate],"ww") for the week, but I need to set the earliest date selected as week one, as this would be the start of the financial year (earliest date is April first) how would I set this up? would I need a table with all the dates in with the week number next to it? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/2007...

Multiple worksheets with non consecutive dates
I have 10 worksheets per workbook. Each worksheet has the same parameters of which I would like to trend. However due to the dates in each worksheet differing (and non consecutive), the chart accepts the dates of the first worksheet as the x-axis and plots all subsequent worksheets in sequential order For exampl Worksheet 1 Worksheet date, paramete Jan 1 44 Jan 29 2 Jan 4 55 Feb 35 8 The chart would use Jan1 and Jan 4 on the x axis and plot worksheet 2 values in the first (Jan 1) and the second (Jan 4) values on the x axis regards Watergirl...

Greatest date per Unit
I am obviously not understanding something very basic with regard to Group BY and Max() sql statements. All I want is to return the entry which has the greatest date value per unit no. I have the following SELECT unit_no, Max(eta_date) AS MaxOfeta_date, tbl_cngu_qt.eta_city FROM tbl_qt GROUP BY cngu_no, eta_city ORDER BY cngu_no; but it returns multiple entries per unit?? What is the proper synthax for this query? QB I came up with the following. It seems to work, but would still like to know if a) this is proper, b) if there is a more efficient method SELECT Tem...

julian date #4
How do you convert a julian date back to a calender date?? When you say Julian Date, do you mean a number like 38353. When you say calendar date, do you mean something like 01/01/2005? If so, this sounds like a formatting question. Put your cursor on the cell with the Julian Date. Select FORMAT / CELLS / NUMBER / DATE. Then in the 'Type:' window, select the desired format. HTH, Gary Brown "Jasonjetdoc" <Jasonjetdoc@discussions.microsoft.com> wrote in message news:5151C4F5-4967-4144-944C-61F84FCD8722@microsoft.com... > How do you convert a julian date bac...

Conditional Format based on Month of Date in Cell
I have a cell that I typed in 1/1/2010 and I want to conditional format it based on what month it is. Say format blue text if it's 1/1/2010 and green text if 2/1/2010. I did a test with formula =IF(MONTH(A1)=1,"yep","nope") and it returns "yep" So I tried to use the following in the conditional formatting window based on formula: MONTH(A1)=1 which does nothing. The formula =MONTH(A1) returns a value of 1 so I don't see what the problem is. Hi Using formulas in conditional formatting, the equtation must always start with an equ...

how to print email without print out today's date?
All I need is to printout the emails from outlook express 6 without printing out today's date that shows up at the right bottom corner of each page. I tried using Advanced button in the print window, change printer properties and all other ways I can think but could not find the solution. I use windows XP. please help me. Do you have a new version of Microsoft Office installed on your computer? If so, see if you have the "Microsoft Office Image Writer" printer installed in your Printer applet in Control Panel. If so, use that printer to save your message and print it or vie...