|
IF Function with Date revised question
Hi,
The previous answer from Michael was great. However, I realized that I will have more than 7 nested If statements. I didn't need the year so, I took it out of the equation. Is there a way to streamline the formula for 12 months? the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0)) cell: D1 will change from January to December (each months return) Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts. As the months change I need the formula to look at the current date and return the previous months prepayment amount. There is a formula from the current return that fills this schedule on a monthly basis. The prepayment schedule looks like this: location dec jan feb etc 1 boca raton 5,000 6,000 7,000 2 St John 2,000 1,000 7,000 etc. The formula will be individual for each location return. Should I use a lookup and range name the location and date? Any suggestions? |
=OFFSET($A$10,,MONTH(D1))
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "taxmom" wrote in message ... Hi, The previous answer from Michael was great. However, I realized that I will have more than 7 nested If statements. I didn't need the year so, I took it out of the equation. Is there a way to streamline the formula for 12 months? the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0)) cell: D1 will change from January to December (each months return) Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts. As the months change I need the formula to look at the current date and return the previous months prepayment amount. There is a formula from the current return that fills this schedule on a monthly basis. The prepayment schedule looks like this: location dec jan feb etc 1 boca raton 5,000 6,000 7,000 2 St John 2,000 1,000 7,000 etc. The formula will be individual for each location return. Should I use a lookup and range name the location and date? Any suggestions? |
Hi Ken,
Thank you for the reply, its nice and short, however can you shed a little more information on the formula? I tried this and I came up with a "value". The month, payments and the cell that will hold the formual are all on difference worksheets in a workbook. Do they need to be next to each other? I assume that the range( $A$10) that will return the payment amount should also include the headings for prior month date? I'm not exactly sure how the data must be positioned to get the desired results. I'm sure this will work for me I just need to set it up properly. Can you give me of an example of this formula? Thank you so much for your help. Debbie "Ken Wright" wrote: =OFFSET($A$10,,MONTH(D1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "taxmom" wrote in message ... Hi, The previous answer from Michael was great. However, I realized that I will have more than 7 nested If statements. I didn't need the year so, I took it out of the equation. Is there a way to streamline the formula for 12 months? the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0)) cell: D1 will change from January to December (each months return) Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts. As the months change I need the formula to look at the current date and return the previous months prepayment amount. There is a formula from the current return that fills this schedule on a monthly basis. The prepayment schedule looks like this: location dec jan feb etc 1 boca raton 5,000 6,000 7,000 2 St John 2,000 1,000 7,000 etc. The formula will be individual for each location return. Should I use a lookup and range name the location and date? Any suggestions? |
Probably my fault, I just reread your message and am wondering if the data
you have in D1 is actually a real date or not. I'm guessing now that it isn't. What the OFFSET formula does in this instance is to take a starting point, and then move away from that cell by x number of columns. The x is determined by the Month number,eg If D1 = Jan, then Month(D1)=1, likewise if D1 = Feb, then Month(D1)=2 etc, AS LONG as the value in D1 is a valid date D1=Jan= 1 == =OFFSET($A$10,,MONTH(D1)) = B10 D1=Feb= 2 == =OFFSET($A$10,,MONTH(D1)) = C10 D1=Mar= 3 == =OFFSET($A$10,,MONTH(D1)) = D10 D1=Apr= 4 == =OFFSET($A$10,,MONTH(D1)) = E10 D1=May=5 == =OFFSET($A$10,,MONTH(D1)) = F10 If it is not a real date, then assuming it is a 3 letter abbreviation in D1 representing the Month, then you can try:- =OFFSET($A$10,,MATCH(D1,{"Jan","Feb","Mar","Apr"," May","Jun","Jul","Aug","Se p","Oct","Nov","Dec"},0)) or if you really want the full month then adjust the formula above, or you can try =OFFSET($A$10,,MATCH(LEFT(D1,3),{"Jan","Feb","Mar" ,"Apr","May","Jun","Jul"," Aug","Sep","Oct","Nov","Dec"},0)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "taxmom" wrote in message ... Hi Ken, Thank you for the reply, its nice and short, however can you shed a little more information on the formula? I tried this and I came up with a "value". The month, payments and the cell that will hold the formual are all on difference worksheets in a workbook. Do they need to be next to each other? I assume that the range( $A$10) that will return the payment amount should also include the headings for prior month date? I'm not exactly sure how the data must be positioned to get the desired results. I'm sure this will work for me I just need to set it up properly. Can you give me of an example of this formula? Thank you so much for your help. Debbie "Ken Wright" wrote: =OFFSET($A$10,,MONTH(D1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "taxmom" wrote in message ... Hi, The previous answer from Michael was great. However, I realized that I will have more than 7 nested If statements. I didn't need the year so, I took it out of the equation. Is there a way to streamline the formula for 12 months? the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0)) cell: D1 will change from January to December (each months return) Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts. As the months change I need the formula to look at the current date and return the previous months prepayment amount. There is a formula from the current return that fills this schedule on a monthly basis. The prepayment schedule looks like this: location dec jan feb etc 1 boca raton 5,000 6,000 7,000 2 St John 2,000 1,000 7,000 etc. The formula will be individual for each location return. Should I use a lookup and range name the location and date? Any suggestions? |
Thank you Ken,
I did a test with the example ranges and now I understand how it works. This will help tremendously, in my job. Sincere thanks, Have a Great Day! "Ken Wright" wrote: Probably my fault, I just reread your message and am wondering if the data you have in D1 is actually a real date or not. I'm guessing now that it isn't. What the OFFSET formula does in this instance is to take a starting point, and then move away from that cell by x number of columns. The x is determined by the Month number,eg If D1 = Jan, then Month(D1)=1, likewise if D1 = Feb, then Month(D1)=2 etc, AS LONG as the value in D1 is a valid date D1=Jan= 1 == =OFFSET($A$10,,MONTH(D1)) = B10 D1=Feb= 2 == =OFFSET($A$10,,MONTH(D1)) = C10 D1=Mar= 3 == =OFFSET($A$10,,MONTH(D1)) = D10 D1=Apr= 4 == =OFFSET($A$10,,MONTH(D1)) = E10 D1=May=5 == =OFFSET($A$10,,MONTH(D1)) = F10 If it is not a real date, then assuming it is a 3 letter abbreviation in D1 representing the Month, then you can try:- =OFFSET($A$10,,MATCH(D1,{"Jan","Feb","Mar","Apr"," May","Jun","Jul","Aug","Se p","Oct","Nov","Dec"},0)) or if you really want the full month then adjust the formula above, or you can try =OFFSET($A$10,,MATCH(LEFT(D1,3),{"Jan","Feb","Mar" ,"Apr","May","Jun","Jul"," Aug","Sep","Oct","Nov","Dec"},0)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "taxmom" wrote in message ... Hi Ken, Thank you for the reply, its nice and short, however can you shed a little more information on the formula? I tried this and I came up with a "value". The month, payments and the cell that will hold the formual are all on difference worksheets in a workbook. Do they need to be next to each other? I assume that the range( $A$10) that will return the payment amount should also include the headings for prior month date? I'm not exactly sure how the data must be positioned to get the desired results. I'm sure this will work for me I just need to set it up properly. Can you give me of an example of this formula? Thank you so much for your help. Debbie "Ken Wright" wrote: =OFFSET($A$10,,MONTH(D1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "taxmom" wrote in message ... Hi, The previous answer from Michael was great. However, I realized that I will have more than 7 nested If statements. I didn't need the year so, I took it out of the equation. Is there a way to streamline the formula for 12 months? the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0)) cell: D1 will change from January to December (each months return) Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts. As the months change I need the formula to look at the current date and return the previous months prepayment amount. There is a formula from the current return that fills this schedule on a monthly basis. The prepayment schedule looks like this: location dec jan feb etc 1 boca raton 5,000 6,000 7,000 2 St John 2,000 1,000 7,000 etc. The formula will be individual for each location return. Should I use a lookup and range name the location and date? Any suggestions? |
You're very welcome :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "taxmom" wrote in message ... Thank you Ken, I did a test with the example ranges and now I understand how it works. This will help tremendously, in my job. Sincere thanks, Have a Great Day! "Ken Wright" wrote: Probably my fault, I just reread your message and am wondering if the data you have in D1 is actually a real date or not. I'm guessing now that it isn't. What the OFFSET formula does in this instance is to take a starting point, and then move away from that cell by x number of columns. The x is determined by the Month number,eg If D1 = Jan, then Month(D1)=1, likewise if D1 = Feb, then Month(D1)=2 etc, AS LONG as the value in D1 is a valid date D1=Jan= 1 == =OFFSET($A$10,,MONTH(D1)) = B10 D1=Feb= 2 == =OFFSET($A$10,,MONTH(D1)) = C10 D1=Mar= 3 == =OFFSET($A$10,,MONTH(D1)) = D10 D1=Apr= 4 == =OFFSET($A$10,,MONTH(D1)) = E10 D1=May=5 == =OFFSET($A$10,,MONTH(D1)) = F10 If it is not a real date, then assuming it is a 3 letter abbreviation in D1 representing the Month, then you can try:- =OFFSET($A$10,,MATCH(D1,{"Jan","Feb","Mar","Apr"," May","Jun","Jul","Aug","Se p","Oct","Nov","Dec"},0)) or if you really want the full month then adjust the formula above, or you can try =OFFSET($A$10,,MATCH(LEFT(D1,3),{"Jan","Feb","Mar" ,"Apr","May","Jun","Jul"," Aug","Sep","Oct","Nov","Dec"},0)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "taxmom" wrote in message ... Hi Ken, Thank you for the reply, its nice and short, however can you shed a little more information on the formula? I tried this and I came up with a "value". The month, payments and the cell that will hold the formual are all on difference worksheets in a workbook. Do they need to be next to each other? I assume that the range( $A$10) that will return the payment amount should also include the headings for prior month date? I'm not exactly sure how the data must be positioned to get the desired results. I'm sure this will work for me I just need to set it up properly. Can you give me of an example of this formula? Thank you so much for your help. Debbie "Ken Wright" wrote: =OFFSET($A$10,,MONTH(D1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "taxmom" wrote in message ... Hi, The previous answer from Michael was great. However, I realized that I will have more than 7 nested If statements. I didn't need the year so, I took it out of the equation. Is there a way to streamline the formula for 12 months? the previous answer =if(and(month(d1)=1,B10,if(and(month(d1)=2,C10,0)) cell: D1 will change from January to December (each months return) Cells: B10, C10, E10, D10, etc for 12 months - contains prepayment amounts. As the months change I need the formula to look at the current date and return the previous months prepayment amount. There is a formula from the current return that fills this schedule on a monthly basis. The prepayment schedule looks like this: location dec jan feb etc 1 boca raton 5,000 6,000 7,000 2 St John 2,000 1,000 7,000 etc. The formula will be individual for each location return. Should I use a lookup and range name the location and date? Any suggestions? |
| All times are GMT +1. The time now is 08:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com