#### calculating months

```hello peeps i'm trying to calculate the number of months from a list of
increasing dates,
b3= 21-Jan-06
b4= 03-Feb-06
and so on
b16= 14-Jul-06
b17= and onwards ar blank
b25= DISPLAY No. MONTHS
i'll be adding more dates in cells b17 to b24 at a later date so would like
it to allow for this to happen
i enter the date as 14/8/06 and it gets displayed as 14-Jul-06 if that helps ?

i've tryed =datevalue(b16)-datevalue(b3) and get this  ##############
```
 0
DarkNight (31)
7/24/2006 7:53:02 PM
excel.newusers 15348 articles. 2 followers.

3 Replies
467 Views

Similar Articles

[PageSpeed] 40

```Hi DarkNight,

Use the DATEDIF() function:

=DATEDIF(B3,B16,"m")

DATEDIF() is only explaind in Help in XL 2000 I believe, (i'm not that up to
date having onlyXL97), but Chip Pearson has a good explanation at:

http://www.cpearson.com/excel/datedif.htm
--
HTH

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"DarkNight" <DarkNight@discussions.microsoft.com> wrote in message
news:7FA61E90-135D-444F-B52C-49421A537B11@microsoft.com...
> hello peeps i'm trying to calculate the number of months from a list of
> increasing dates,
> b3= 21-Jan-06
> b4= 03-Feb-06
> and so on
> b16= 14-Jul-06
> b17= and onwards ar blank
> b25= DISPLAY No. MONTHS
> i'll be adding more dates in cells b17 to b24 at a later date so would
> like
> it to allow for this to happen
> i enter the date as 14/8/06 and it gets displayed as 14-Jul-06 if that
> helps ?
>
> i've tryed =datevalue(b16)-datevalue(b3) and get this  ##############

```
 0
sandymann2 (1054)
7/24/2006 8:25:05 PM
```Thanks Sandy Mann yer a star.. yet again.

"Sandy Mann" wrote:

> Hi DarkNight,
>
> Use the DATEDIF() function:
>
> =DATEDIF(B3,B16,"m")
>
> DATEDIF() is only explaind in Help in XL 2000 I believe, (i'm not that up to
> date having onlyXL97), but Chip Pearson has a good explanation at:
>
> http://www.cpearson.com/excel/datedif.htm
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
>
> "DarkNight" <DarkNight@discussions.microsoft.com> wrote in message
> news:7FA61E90-135D-444F-B52C-49421A537B11@microsoft.com...
> > hello peeps i'm trying to calculate the number of months from a list of
> > increasing dates,
> > b3= 21-Jan-06
> > b4= 03-Feb-06
> > and so on
> > b16= 14-Jul-06
> > b17= and onwards ar blank
> > b25= DISPLAY No. MONTHS
> > i'll be adding more dates in cells b17 to b24 at a later date so would
> > like
> > it to allow for this to happen
> > i enter the date as 14/8/06 and it gets displayed as 14-Jul-06 if that
> > helps ?
> >
> > i've tryed =datevalue(b16)-datevalue(b3) and get this  ##############
>
>
>
```
 0
DarkNight (31)
7/24/2006 8:59:01 PM
```You're very welcome

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"DarkNight" <DarkNight@discussions.microsoft.com> wrote in message
news:8C69E528-3EA1-4771-9092-2B44307927D9@microsoft.com...
> Thanks Sandy Mann yer a star.. yet again.
>
> "Sandy Mann" wrote:
>
>> Hi DarkNight,
>>
>> Use the DATEDIF() function:
>>
>> =DATEDIF(B3,B16,"m")
>>
>> DATEDIF() is only explaind in Help in XL 2000 I believe, (i'm not that up
>> to
>> date having onlyXL97), but Chip Pearson has a good explanation at:
>>
>> http://www.cpearson.com/excel/datedif.htm
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>>
>> sandymann2@mailinator.com
>> Replace@mailinator.com with @tiscali.co.uk
>>
>>
>> "DarkNight" <DarkNight@discussions.microsoft.com> wrote in message
>> news:7FA61E90-135D-444F-B52C-49421A537B11@microsoft.com...
>> > hello peeps i'm trying to calculate the number of months from a list of
>> > increasing dates,
>> > b3= 21-Jan-06
>> > b4= 03-Feb-06
>> > and so on
>> > b16= 14-Jul-06
>> > b17= and onwards ar blank
>> > b25= DISPLAY No. MONTHS
>> > i'll be adding more dates in cells b17 to b24 at a later date so would
>> > like
>> > it to allow for this to happen
>> > i enter the date as 14/8/06 and it gets displayed as 14-Jul-06 if that
>> > helps ?
>> >
>> > i've tryed =datevalue(b16)-datevalue(b3) and get this  ##############
>>
>>
>>

```
 0
sandymann2 (1054)
7/24/2006 9:33:48 PM

Similar Artilces:

Calculated Field based on 2 Running Totals
I need to make a calculated field based on two running totals: Week Metric1 Metric2 Ratio 1 How about a little more detail. "InfiniteJoy" <chris.damsgard@gmail.com> wrote in message news:052eeb76-4ca7-42a4-9952-390fedb4a2a7@24g2000hsh.googlegroups.com... > I need to make a calculated field based on two running totals: > > Week Metric1 Metric2 Ratio > 1 ...

what does "calculate" mean in status bar?
In a single file, when it is opened, the word "calculate" appears on the status bar on the left next to the word ready. Can anyone tell me what this means and why it only appears in a particular file. On Nov 16, 12:30=A0pm, eman <e...@discussions.microsoft.com> wrote: > In a single file, when it is opened, the word "calculate" appears on the > status bar on the left next to the word ready. =A0Can anyone tell me what= this > means and why it only appears in a particular file. Check under "Tools>Options>Calculation" to see whether...

office problems on a month-old macbook
I installed Office a month ago when I first purchased my Macbook. Everything worked well until this past week when none of my Office programs worked. I just double click on the icons and nothing happens. The programs I double click do show up in "Recent Programs," but the programs don't open. I'm not sure why it stopped working all of a sudden. I tried deleting the program, but the "Remove Office" option wouldn't work. Again, I'd click the icon, and nothing would happen. I ended up dragging the file into the trash and trying to reinstall. I couldn't use...

How to calculate a Time Difference
I currently am looking to be able to subtract two times (using the 24 hour clock), where the result is a Decimal. For example. I leave at 14:00 and arrive at 17:30. Thats a difference of 3.5 hours. I believe I have this solved with the following formuals: =(C2-INT(C2))*24-(B2-INT(B2))*24 (WHERE C2 is the arrival time and B2 is the DEPARTURE Time). My problem is that if you leave at 23:30 and don't arrive until 03:30, then it gives me a large negative number instead of 4.0 hours. Any ideas? Thanks, Try: =(C2-B2+(B2>C2))*24 And format the cell as #.0. For more info on su...

Z Report for Multiple Days/weeks/months
Does anyone know where I can get a report that will give me the daily Z report data but in column data by date? Right now I have to manualy enter every Z report into a spreadsheet. The data is obviously in the database but there is no obvious way of getting it out. I DO NOT want to just REPRINT Z Reports. I want a full month or quarters worth of data in a single report but still broken down by day. This will allow me to save as a CSV file then import into Excel where I can then massage the data as necessary. Please help, I get a lot of info from this data like the ability to analyz...

Count Number Of Employees for Month and Year historically
how i would i get the number of employees for each month and year that we have data for? my fields are : Representative_Name Approval_Date Termination_Date my data goes back to 06/2007. i can get how many person's were hired/fired in a particular month/year in history but a total number of un-terminated persons up to that month in history is eluding me. hope that makes sense. AC2007/XP. It depends on how you define 'the number of employees for each month'. Do you measure at the start of the month or the end? What do you want to do with an employee who ...

Excel 2007 problems with AS 2005 calculated members
There is a big trouble using calculated members of Analysis Services (AS) 2005 with Excel 2007. We have a dimension with a lot of calculated members (more than 100). First of all, you need to change PivotTable options to enable the display of calculated members (check PivotTable Tools / Options / Options / Display / Show calculated members from OLAP server). When you do that, you choose to get all calculated members available from a dimension/attribute. Now, if you have 100 calculated members into an attribute, chances are that you want to select only one or two of those members. Unfortunatel...

Track Sales Weekly, Monthly and Annually with this Powerful Tool 25994

Any pitfalls to running an ADC from 5.5 to 2003 for 6 months?
We're budgeted for a single Exchange 2003 server immediately which I would like to place all new users on. I won't have budget to upgrade the other 6 Exchange 5.5 servers for at least 6 months. Are there any known pitfalls or issues with running an ADC between the new Exchange 2003 server and our Exchange 5.5 sites for an extended period of time. We have already upgraded all of our DC's to Windows 2003. Thanks for any information, Dan We co-existed in a 5.5/2000 environment for over year with minimal problems. Just make sure your ADC connector are setup correctly and replica...

Calculating Billing, hours * rate
Is there a way to calculate billing? I have a spread sheet with column C for 'time in' and column D for 'time out' and column E for the difference (hours worked). These columns are formatted as time. Column F is the billing rate expressed as currency. I'm trying to calculate the billing by multiplying the hours worked times the hourly billing rate(Col E * Col F), but it's not working. Can this calculation be done in Excel? If so, then how... Thank you Hi Tony See this page http://www.cpearson.com/excel/overtime.htm And the topic page http://www.cpearson.com/exc...

CRM 3.0 Tax Calculation
I tried to automate tax calculation for the Quote Product form (can also be the Order- or Invoice Product form, principle is the same) in an onChange event, simplified like this: crmForm.all.tax.DataValue = crmForm.all.baseamount.DataValue * TaxRate; The problem is that the field crmForm.all.baseamount.DataValue is updated after a Save or SaveAndClose, so after a possible onChange event. Also using the onSave event of the form does not work, the crmForm.all.baseamount.DataValue is calculated or updated after the event (?). Question: How/Where are the fields crmForm.all.baseamount.DataV...

Intuit iPhone-friendly Quicken priced at \$3/month
http://snipurl.com/1vk5m "Quicken's U.S. market share rose to 80 percent in the first 11 months of this year from 72 percent in 2006. Microsoft Money's share of sales declined to 19 percent from 25 percent in the prior year, according to NPD." "Brent" <undefined> wrote in message news:OTqBzwoQIHA.1184@TK2MSFTNGP04.phx.gbl... > http://snipurl.com/1vk5m It is not hard to believe. It's also hard to see that MS is doing anything, or cares to do anything, to arrest the slide. "Brent" <undefined> wrote in message news:u0XTH4o...

Forumla to calculate a percentage
I have a column of figures that I need to calculate that if the figure in column A is Transport costs to us is �100 and column B is the price sold to the customer is �25.00, i need the third column to work out < 72% Theoretical Transport Recovery in other words if the value in column B is <72% mark it red Like wise for fuel is the value in column B is <132% mark it red Try as I might I cannot get it to work it out any ideas Julie, This formula gives the correct percentage... =(B1-A1)/A1 Format the formula cell with this custom number format... 0%;[Red]-0% If you don'...

age calculation #3
I am trying to find a calculation for "how old will this person be on this date" example: Johnny was born 08/10/1910 and I want to know as of 07/01/2005 how old Johnny will be, without rounding up. Answer: 94 years old Is this even possible? http://www.xldynamic.com/source/xld.html Regards, Peo Sjoblom "Teapot" wrote: > I am trying to find a calculation for "how old will this person be on this > date" > > example: Johnny was born 08/10/1910 and I want to know as of 07/01/2005 how > old Johnny will be, without rounding up. > ...

Month and Year Only
Is there any way to enter just the month and year without the day? For instance, if I enter 4/6 (for April 2006) it defaults to April 6, 2004. I would like for it to read it as April 2006. Thank you. As far as I know, there is no way to enter only month and year. You ca however make it look the way you want on the sheet through th formatting process. First, fortmat the cells, using custom format, to mmmm yyyy. Then, yo will either have to enter 4/1/6 or 4/2006. Either way, the actua entry will be April 1, 2006. It will appear as "April 2006" because o the formatting -- Mess...

how to calculate between times
hello all can someone pls advice. i need to calculate the time between start to finish dates. A1 08/07/05 12:00 A2 09/07/05 1200 i found this formula =text(a2-a1,"h:mm") to be helpful. but i want to calculate business hours only which is from 9am to 5:30. can someone pls advice what formula i can use? thanks -- noelf ------------------------------------------------------------------------ noelf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6979 View this thread: http://www.excelforum.com/showthread.php?threadid=386045 Try this =17.5/24-(A1-R...

Report calculation
Hello i have a report which shows jobs data we have inputed! it shows Job id as a field and if the job was filled it shows the placement ID number (like below) jobID company placedID 11111 test 22222 33333 test33 44444 test44 66666 what i want to do is two fold 1) if there is a a value in the placedID field then the field returns 'Placed' instead of the number (i would imagine adding an unbound text box and entering a expression) also i want to 'count' the placedID fields if there is a value in it! any help will be appreciated thanks S, 1)...

Time Calculation #3
This is a multi-part message in MIME format. ------=_NextPart_000_0034_01C683C3.25042D90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to set up a Time Calc as below? A1=3D 10:30AM A2=3D10:35AM A3=3D10:40AM A4=3D10:45AM A5=3D10:50AM A6=3D10:55AM A7=3D11:00AM A8=3D11:05AM A9=3D11:10AM A10=3D11:15AM. If i place the time in A1 and a Time in A10(45mins later) can excel = formulate A2-A9 at 5 min increments to save manually entering each row = time value in? How? ------=_NextPart_000_0034_01C683C3.25042D90 Content-Type: tex...

Calculating averages and not including blank cells
Hello all, Could someone tell me how to calculate the average of a column in Excel that has blanks? I don't want to include the blanks in the average. I tried using: =AVERAGE(E1:E18) thinking that it would not include the blank cells as part of the calculation, but then realized the error of my ways... Your help is most appreciated! M. -- Of all the things I''ve lost, I miss my mind the most. But AVERAGE does ignore blank cells best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Mercedes" <Mercedes@...

How to view credit card balance by month?
How can I view a credit card balance by month so I can see how much it has gone up or done during some period? Thanks, Brett In microsoft.public.money, Brett wrote: >How can I view a credit card balance by month so I can see how much it has >gone up or done during some period? You could customize the WhatIHave->NetWorthOverTime to include just your credit card account(s). "Cal Learner-- MVP" <via_newsgroup@please.tnx> wrote in message news:vvu9k015iccau0sjjkn5rh8i92mbe2r4qe@4ax.com... > In microsoft.public.money, Brett wrote: > >>How can I view...

Calculating new date by entering number of additional workdays
Would love some assistance with the following: If I have a start date in a cell (A1), I am hoping to enter a number of additional workdays into another cell (B1), resulting in a new date -- skipping weekends -- in a third cell (C1). Looking for the formula for C1. If it helps, here's an example: * Start date = 4/1/10 (which is a Thursday) * Additional workdays = 5 * Solution = 4/7/10 (because it skipped the weekend) Thanks in advance. Hi Ensure that you have the Analysis Toolpak loaded. Tools>Addins>Analysis Toolpak then with startdate in A1, number of days t...

Query: Calculating
Hi, I have a table A. Table A has 2 fields - Sale1 and Sale2. I want to create a query that will sum the value in Sale1 and Sale2. In the query I wrote ---> Output: [Sale1]+[Sale2] The problem I have is that if either value in Sale1 or Sale2 is null, the Output return Null value. But I want it to show the value and treat null as 0. For instance, 5+Null =5. thanks, Boon On Thu, 25 Feb 2010 11:31:11 -0600, Boon wrote: > Hi, > > I have a table A. Table A has 2 fields - Sale1 and Sale2. I want to create a > query that will sum the value in Sale1 and ...

Get Month and Year from death field
I havedate/time field that stores value like 2010-4-10 00:00:00 I need to get the month and year like Apr-2010 or April-2010 I tried... (DATEPART(MONTH,WRKODR.DateDel) + DATEPART(Year, WRKODR.DateDel)) As MTYr It returns 2010 in some cases 2011... I think it just add month to year... I need help Thanks in advance After more googling I tried (DateName(Month, WRKODR.DateDel) + ' ' + DateName(Year, WRKODR.DateDel)) As MTYr It pulls date and month correctly as in April 2010 Any "inherent danger" in using datename? "jpBless&q...

tools helping understand calculations
Hello I have to understand high volume calculations in a multisheet Excel file. Are there tools in Excel except the Formula auditing tools that could help understand? Start here http://www.decisionmodels.com/calcsecrets.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message news:OroJmqlxFHA.2136@TK2MSFTNGP10.phx.gbl... > Hello > I have to understand high volume calculations in a multisheet Excel file. Are there tools in Excel except the Formula auditing > tools that could help understand? > I have ...

What if formulas and calculations
I need to create a calculation based upon a number of fields e.g. A1 = Y or N B1 = Y or N C1 = 32 D1 = a number E1 is for the result Required: If A1 = Y and B1 = Y then E1 = C1 * D1 else E1 = D1 All I can get is FALSE if either A1 or B1 is not Y but is both are Y then I get the correct result =IF(AND(A1="Y",B1="Y"),C1*D1,D1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "BJS" <BJS@discussions.microsoft.com> wrote in message news:60CBD837-9F0A-4AB9-88CC-7493705AAA33@microsoft.com... >I need to create a calculation based upon a number of fie...