| View previous topic :: View next topic |
| Author |
Message |
tnuag
General User
Joined: 26 Jun 2007
Posts: 5
Location: England
|
 Posted: Tue Nov 16, 2010 10:51 pm Post subject: Date interval |
 |
|
| I need to calculate the number of complete months since an order was delivered - how can I do this in a spredsheet? |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Wed Nov 17, 2010 12:22 am Post subject: |
|
|
If the received date is in A1 then try...
| Code: | =MONTHS(IF(DAY(A1)<>1;DATE(YEAR(A1);MONTH(A1)+1;1);A1);IF(MONTH(TODAY())<>MONTH(TODAY()+1);TODAY()+1;DATE(YEAR(TODAY());MONTH(TODAY());1));0)
|
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
tnuag
General User
Joined: 26 Jun 2007
Posts: 5
Location: England
|
| Posted: Wed Nov 17, 2010 11:31 pm Post subject: |
|
|
Thanks for trhat - when I tried it it gave an answer which was one month short.
My dates and results were as follows:
1 Nov 2007 - 36
4 Jan 2009 - 21
27 Jan 2009 - 21
31 Mar 2009 - 19
2 Sep 2009 - 13
28 Sep 2009 - 13
20 Nov 2009 - 11
1 Mar 2010 - 8
13 Mar 2010 - 7
10 Apr 2010 - 6
30 May 2010 - 5
19 Jun 2010 - 4
20 Aug 2010 - 2
1 Oct 2010 - 1
29 Oct 2010 - 0
19 Nov 2010 - -1
So I put the expressio into an intermediate (hidden) field, then put that cell +1 into the answer cell.
Many thanks for your help. |
|
| Back to top |
|
|
|
