Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify if Cell is Formula or written number | Excel Worksheet Functions | |||
How do I sort by date (not days, weeks, months) in Excel 2000? | Excel Discussion (Misc queries) | |||
need help with formula | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
How do I replace a negative number at the end of a formula with a. | Excel Discussion (Misc queries) |