Microsoft Excel formulae....... How to do this?
|
|
 May 1 2008, 11:20 PM, updated 18y ago
Show posts by this member only | Post
#1
|
 
Senior Member
684 posts Joined: Dec 2007  
|
well i wanted to google but i cant think of the key words so...
 i want a formula to count Months from one point to ANOTHER point and NOT a formula to count Months from one point to CURRENT point/time does anyone know how to do this?? i want to do something like "If point A to B is 10 months, do action A" and "If point A to B is 20months, do action B" help pls |
|
|
|
|
|
May 2 2008, 01:22 AM
Show posts by this member only | Post
#2
|
Senior Member
876 posts Joined: Jan 2003 From: Tokyo, London, Singapore, KL, Space  
|
Its best if you ask this specific question from Software or Codemaster section.
(Q&A is like for general questions) You'll have much better chances in those 2 sections, gudluck  |
|
|
May 2 2008, 12:13 PM
Show posts by this member only | Post
#3
|
Junior Member
431 posts Joined: Apr 2008
|
didn't know excel comes with if statement...
|
|
|
May 2 2008, 02:04 PM
Show posts by this member only | Post
#4
|
Senior Member
591 posts Joined: Jan 2007 From: the interWebs...
|
quite difficult to do in excel because of how excel lack features/functions that involves manipulation/calculations based on dates...not impossible but will be quite complicated. i tried and it ain't pretty...
|
|
|
May 2 2008, 10:13 PM
Show posts by this member only | Post
#5
|
Junior Member
336 posts Joined: Dec 2004
|
use the IF() function, if(logic, true, false) eg =if(C1=10, B2+C2, B3+C3)
it's like if c1=10, then B2+C2, else B3+C3 or you can do multiple loop/condition inside eg =if(if(if(......) ) ) |
|
|
May 2 2008, 10:18 PM
Show posts by this member only | Post
#6
|
|
Senior Member
684 posts Joined: Dec 2007
|
QUOTE(firedauz @ May 2 2008, 01:22 AM) Its best if you ask this specific question from Software or Codemaster section. ah yah but would that be double posting?(Q&A is like for general questions) You'll have much better chances in those 2 sections, gudluck  QUOTE(PSY84 @ May 2 2008, 10:13 PM) use the IF() function, if(logic, true, false) eg =if(C1=10, B2+C2, B3+C3) yeah i know that IF function and logical test ETC but i wanna know how to set one logical test to calculate months???it's like if c1=10, then B2+C2, else B3+C3 or you can do multiple loop/condition inside eg =if(if(if(......) ) ) |
|
|
|
|
|
May 2 2008, 10:23 PM
Show posts by this member only | Post
#7
|
|
Senior Member
876 posts Joined: Jan 2003 From: Tokyo, London, Singapore, KL, Space
|
It should be understandable even if its double-posting.
"Not alot of people are able to answer it here" is the important factor in this case. |
|
|
May 8 2008, 03:05 PM
Show posts by this member only | Post
#8
|
Senior Member
2,711 posts Joined: Sep 2005
|
oooo... i love excel challenges
Do you have a lot of different conditions or just two? If it's just two, here goes:- Let's say you have date 1 on cell A1, and date 2 on cell B1 (I.e, you want to make a condition based on the number of months date 2 is from date 1) First you need a simple formula to count the difference in months which is this:- =(MONTH(B1)-MONTH(A1))+((YEAR(B1)-YEAR(A1))*12) Next to create the IF statement:- =IF((MONTH(B1)-MONTH(A1))+((YEAR(B1)-YEAR(A1))*12)=10,"Action A",IF((MONTH(B1)-MONTH(A1))+((YEAR(B1)-YEAR(A1))*12)=20,"Action B","No condition matched!")) Done! |
|
|
May 8 2008, 03:07 PM
Show posts by this member only | Post
#9
|
|
Senior Member
2,280 posts Joined: Jun 2006
|
use datediff la brother.
gile panjang kod, tapi tak betul muahaha. |
|
|
May 8 2008, 03:15 PM
|
|
Senior Member
2,711 posts Joined: Sep 2005
|
QUOTE(dopodplaya @ May 8 2008, 03:07 PM) use datediff la brother. datediff??gile panjang kod, tapi tak betul muahaha. What is the syntax like, could you list it out? |
|
|
May 8 2008, 03:20 PM
|
|
Senior Member
2,280 posts Joined: Jun 2006
|
The syntax for DATEDIF is as follows:
CODE =DATEDIF(Date1, Date2, Interval) Where: Date1 is the first date, Date2 is the second date, Interval is the interval type to return. Example - different of date in month CODE =DATEDIF(Date1,Date2,"m") QUOTE Code for Interval This post has been edited by dopodplaya: May 8 2008, 03:21 PMm - Months Complete calendar months between the dates. d - Days Number of days between the dates. y - Years Complete calendar years between the dates. ym - Months Excluding Years Complete calendar months between the dates as if they were of the same year. yd - Days Excluding Years Complete calendar days between the dates as if they were of the same year. md - Days Excluding Years And Months Complete calendar days between the dates as if they were of the same month and same year. |
|
|
May 8 2008, 03:37 PM
|
Senior Member
3,589 posts Joined: Nov 2004
|
I just use date2 - date1 = answer
 |
|
|
May 8 2008, 03:51 PM
|
|
Senior Member
2,711 posts Joined: Sep 2005
|
QUOTE(dopodplaya @ May 8 2008, 03:20 PM) The syntax for DATEDIF is as follows: Cool! Didn't know of such function.CODE =DATEDIF(Date1, Date2, Interval) Where: Date1 is the first date, Date2 is the second date, Interval is the interval type to return. Example - different of date in month CODE =DATEDIF(Date1,Date2,"m") Thanks for sharing! QUOTE(suiteng @ May 8 2008, 03:37 PM) I just use date2 - date1 = answer that would give the difference in days, so won't work for this case. |
| Change to: |  0.0190sec
 0.69
 5 queries
 GZIP Disabled
Time is now: 10th December 2025 - 08:12 PM |
Quote