If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMIF Formula (I think...)
Hi,
I have a workbook (well obviously or I wouldn't be here...), Excel 2003. One sheet is a running sheet containing a summary of invoices for the month - one invoice per row. A10 contains the date of the invoice, G10 contains a code, eg 3, which equals 21days. What I want to do is this: IF G10=3, then A10 + 21, or IF G10 = 4, then A10 + 30(days) or if G10 = 5 then add 30 days from End Of Month (have no idea how to display this). I want Column H to display the due date of payment, and be highligted if past due. Is this possible? Have been playing around with some formulas, but so far no joy. -- Thanks for the help |
#2
|
|||
|
|||
SUMIF Formula (I think...)
in H10, =IF(G10=3,A10+21,IF(G10=4,A10+30,IF(G10=5,EOMONTH( A10,0)+30,""))) Then select column H and go to Format|Conditional Formatting... Set parameters: Cell Value Greater Than =Today() Click Format and choose from Pattern tab. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948 |
#3
|
|||
|
|||
SUMIF Formula (I think...)
Hi NBVC,
Thank you so much for your formula, it works perfectly for 3 & 4, however 5 doesn't work - it's returning the #NAME? error. -- Thanks for the help "NBVC" wrote: in H10, =IF(G10=3,A10+21,IF(G10=4,A10+30,IF(G10=5,EOMONTH( A10,0)+30,""))) Then select column H and go to Format|Conditional Formatting... Set parameters: Cell Value Greater Than =Today() Click Format and choose from Pattern tab. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948 |
#4
|
|||
|
|||
SUMIF Formula (I think...)
JS;462696 Wrote: Hi NBVC, Thank you so much for your formula, it works perfectly for 3 & 4, however 5 doesn't work - it's returning the #NAME? error. -- Thanks for the help "NBVC" wrote: in H10, =IF(G10=3,A10+21,IF(G10=4,A10+30,IF(G10=5,EOMONTH( A10,0)+30,""))) Then select column H and go to Format|Conditional Formatting... Set parameters: Cell Value Greater Than =Today() Click Format and choose from Pattern tab. -- NBVC Where there is a will there are many ways. 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/members/nbvc.html) View this thread: 'SUMIF Formula (I think...) - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=127948) EOMONTH() is an analysis toolpak addin function... Go to Tools|Addins and check the Analysis Toolpak addin.. click Ok...to install -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948 |
#5
|
|||
|
|||
SUMIF Formula (I think...)
You can also use: DATE(YEAR(A10),MONTH(A10)+1,0)
to replace: EOMONTH(A10,0) in NBVC's expression btw, do hit the YES's (like the one below) in all responses which help -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "JS" wrote: .. however 5 doesn't work - it's returning the #NAME? error |
#6
|
|||
|
|||
SUMIF Formula (I think...)
Max;462716 Wrote: You can also use: DATE(YEAR(A10),MONTH(A10)+1,0) to replace: EOMONTH(A10,0) in NBVC's expression btw, do hit the YES's (like the one below) in all responses which help -- Max Singapore 'Savefile - Free File Upload' (http://savefile.com/projects/236895) Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "JS" wrote: .. however 5 doesn't work - it's returning the #NAME? error I did mention that -- NBVC Where there is a will there are many ways. 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948 |
#7
|
|||
|
|||
SUMIF Formula (I think...)
Hi Max,
Thank you for your response. The formula works, however it's returning the incorrect date. If the date in column A is say 12/07/09, it returns 31/07/09 when it needs to return 31/08/09. Sorry if I hadn't made that clear. -- Thanks for the help "Max" wrote: You can also use: DATE(YEAR(A10),MONTH(A10)+1,0) to replace: EOMONTH(A10,0) in NBVC's expression btw, do hit the YES's (like the one below) in all responses which help -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "JS" wrote: .. however 5 doesn't work - it's returning the #NAME? error |
#8
|
|||
|
|||
SUMIF Formula (I think...)
Just easily adjust the MONTH part of it to suit, viz.:
DATE(YEAR(A10),MONTH(A10)+2,0) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "JS" wrote: Hi Max, Thank you for your response. The formula works, however it's returning the incorrect date. If the date in column A is say 12/07/09, it returns 31/07/09 when it needs to return 31/08/09. Sorry if I hadn't made that clear. |
#9
|
|||
|
|||
SUMIF Formula (I think...)
Perfect Max!
-- Thanks for the help! "Max" wrote: Just easily adjust the MONTH part of it to suit, viz.: DATE(YEAR(A10),MONTH(A10)+2,0) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "JS" wrote: Hi Max, Thank you for your response. The formula works, however it's returning the incorrect date. If the date in column A is say 12/07/09, it returns 31/07/09 when it needs to return 31/08/09. Sorry if I hadn't made that clear. |
#10
|
|||
|
|||
SUMIF Formula (I think...)
Welcome, glad to hear
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "JS" wrote in message ... Perfect Max! -- Thanks for the help! |
Thread Tools | |
Display Modes | |
|
|