Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cowtoon
 
Posts: n/a
Default Need number of months or weeks passed from formula

Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
use DATEDIF. See:
http://www.cpearson.com/excel/datedif.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Cowtoon" schrieb im Newsbeitrag
...
Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than

what
this provides. This formula results in full month calculations only.

Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at

number of
months, but I don't know the "name" for how to count weeks. The

formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier

postings
(which had a different original question) to "general" newsgroup, but

I was
afraid it might get missed because it's not the original question.

My
apologies if this is inappropriate posting behaviour.


  #3   Report Post  
Daniel.M
 
Posts: n/a
Default

HI,

http://groups.google.com/groups?hl=f...rum-nospam.com

Click on the "Complete Thread" link on that page.

HTH

Daniel M.

"Cowtoon" wrote in message
...
Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.



  #4   Report Post  
Cowtoon
 
Posts: n/a
Default

Hi Daniel, thanks for the response. I look through the thread and tried a
few of the examples (changing the cells, as appropriate, of course), but it's
not giving the correct results, so I'm not sure what I'm doing wrong. I'm
happy to have a month results of 4.3 or 4.5. I don't need exact number of
days, just the time that has lapsed. I'd even take weeks, then I could
convert to months or portion.

There's gotta be a way to do this.
Thanks again. Diana

"Daniel.M" wrote:

HI,

http://groups.google.com/groups?hl=f...rum-nospam.com

Click on the "Complete Thread" link on that page.

HTH

Daniel M.

"Cowtoon" wrote in message
...
Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.




  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 10 Nov 2004 09:29:13 -0800, "Cowtoon"
wrote:

Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.


Diana,

One problem with "exact" is that months vary in length.

Two suggestions:

1. To compute the number of weeks between your two dates:

=(C2-B2)/7

2. To compute months and days:

Months -- =DATEDIF(B2,C2,"m")
Remaining Days -- =DATEDIF(B2,C2,"md")


--ron


  #6   Report Post  
Cowtoon
 
Posts: n/a
Default

Thanks Ron, I'll play with it and let you know how it works out.
Diana

"Ron Rosenfeld" wrote:

On Wed, 10 Nov 2004 09:29:13 -0800, "Cowtoon"
wrote:

Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.


Diana,

One problem with "exact" is that months vary in length.

Two suggestions:

1. To compute the number of weeks between your two dates:

=(C2-B2)/7

2. To compute months and days:

Months -- =DATEDIF(B2,C2,"m")
Remaining Days -- =DATEDIF(B2,C2,"md")


--ron

  #7   Report Post  
Cowtoon
 
Posts: n/a
Default

Ron,
It would appear that I don't know enough about these formulas to encorporate
your suggestion into my existing formula or I'm not sure what to replace.
I'm getting an "error", so I'm doing something wrong.

Any thoughts?

"Ron Rosenfeld" wrote:

On Wed, 10 Nov 2004 09:29:13 -0800, "Cowtoon"
wrote:

Hi,
I used the following formula to calculate the number of months passed
between two dates. Unfortunately, I need a little more accuracy than what
this provides. This formula results in full month calculations only. Is
there a way to get a more accurate calculation. I wouldn't even mind
calculating the number of weeks and dividing by 4.28 to arrive at number of
months, but I don't know the "name" for how to count weeks. The formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier postings
(which had a different original question) to "general" newsgroup, but I was
afraid it might get missed because it's not the original question. My
apologies if this is inappropriate posting behaviour.


Diana,

One problem with "exact" is that months vary in length.

Two suggestions:

1. To compute the number of weeks between your two dates:

=(C2-B2)/7

2. To compute months and days:

Months -- =DATEDIF(B2,C2,"m")
Remaining Days -- =DATEDIF(B2,C2,"md")


--ron

  #8   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
what is the exact formula you have tried and what error do you get?

--
Regards
Frank Kabel
Frankfurt, Germany

"Cowtoon" schrieb im Newsbeitrag
...
Ron,
It would appear that I don't know enough about these formulas to

encorporate
your suggestion into my existing formula or I'm not sure what to

replace.
I'm getting an "error", so I'm doing something wrong.

Any thoughts?

"Ron Rosenfeld" wrote:

On Wed, 10 Nov 2004 09:29:13 -0800, "Cowtoon"
wrote:

Hi,
I used the following formula to calculate the number of months

passed
between two dates. Unfortunately, I need a little more accuracy

than what
this provides. This formula results in full month calculations

only. Is
there a way to get a more accurate calculation. I wouldn't even

mind
calculating the number of weeks and dividing by 4.28 to arrive at

number of
months, but I don't know the "name" for how to count weeks. The

formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a

point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier

postings
(which had a different original question) to "general" newsgroup,

but I was
afraid it might get missed because it's not the original question.

My
apologies if this is inappropriate posting behaviour.


Diana,

One problem with "exact" is that months vary in length.

Two suggestions:

1. To compute the number of weeks between your two dates:

=(C2-B2)/7

2. To compute months and days:

Months -- =DATEDIF(B2,C2,"m")
Remaining Days -- =DATEDIF(B2,C2,"md")


--ron


  #9   Report Post  
Cowtoon
 
Posts: n/a
Default

Frank,
I think I figured it out (I don't give up all that easy, but didn't think
I'd figure it out).

What I didn't try from Ron's post is to use what he suggested to calculate
the number of weeks. I used that calculation, then divided by 4.28 (assume
that's correct to calculate number of months) an that seems to have solved my
formula woes. I used the week calculation because some the dates that I'm
using are from a different year. It appears (from what I've read) that month
calculations don't work correctly if they are not in the same year.

Here's Ron post. I simply used his 1. suggestion, then divided by 4.28 to
calculate the number of months. Does that sound about right to you?

One problem with "exact" is that months vary in length.
Two suggestions:
1. To compute the number of weeks between your two dates:
=(C2-B2)/7
2. To compute months and days:
Months -- =DATEDIF(B2,C2,"m")
Remaining Days -- =DATEDIF(B2,C2,"md")

"Frank Kabel" wrote:

Hi
what is the exact formula you have tried and what error do you get?

--
Regards
Frank Kabel
Frankfurt, Germany

"Cowtoon" schrieb im Newsbeitrag
...
Ron,
It would appear that I don't know enough about these formulas to

encorporate
your suggestion into my existing formula or I'm not sure what to

replace.
I'm getting an "error", so I'm doing something wrong.

Any thoughts?

"Ron Rosenfeld" wrote:

On Wed, 10 Nov 2004 09:29:13 -0800, "Cowtoon"
wrote:

Hi,
I used the following formula to calculate the number of months

passed
between two dates. Unfortunately, I need a little more accuracy

than what
this provides. This formula results in full month calculations

only. Is
there a way to get a more accurate calculation. I wouldn't even

mind
calculating the number of weeks and dividing by 4.28 to arrive at

number of
months, but I don't know the "name" for how to count weeks. The

formula
won't accept "WEEK" as a name like it does for "MONTH".
=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) and it works to a

point.

Thanks for any help. Diana
PS ... I posted this request in a reply to one of my own earlier

postings
(which had a different original question) to "general" newsgroup,

but I was
afraid it might get missed because it's not the original question.

My
apologies if this is inappropriate posting behaviour.

Diana,

One problem with "exact" is that months vary in length.

Two suggestions:

1. To compute the number of weeks between your two dates:

=(C2-B2)/7

2. To compute months and days:

Months -- =DATEDIF(B2,C2,"m")
Remaining Days -- =DATEDIF(B2,C2,"md")


--ron



  #10   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Fri, 12 Nov 2004 09:21:03 -0800, "Cowtoon"
wrote:

Ron,
It would appear that I don't know enough about these formulas to encorporate
your suggestion into my existing formula or I'm not sure what to replace.
I'm getting an "error", so I'm doing something wrong.

Any thoughts?


What error?

What formula? (I suggested two different approaches).


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identify if Cell is Formula or written number Claes G Excel Worksheet Functions 13 December 5th 07 06:01 PM
How do I sort by date (not days, weeks, months) in Excel 2000? Tony Excel Discussion (Misc queries) 1 January 21st 05 04:28 PM
need help with formula Mike Busch Excel Discussion (Misc queries) 2 January 6th 05 03:57 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 08:18 AM
How do I replace a negative number at the end of a formula with a. dealn2 Excel Discussion (Misc queries) 5 December 23rd 04 08:47 PM


All times are GMT +1. The time now is 11:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"