Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
I need help making a formula to use the date in cell F3, add 60 days and then
find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
Hi,
Try this =IF(DAY(F3+60)=1,F3+60,EOMONTH(F3+60,0)+1) If you get a NAME error then Tools|Addins Check the analysis toolpak and re=enter the formula Mike "NeSchw6G" wrote: I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
You can use this formula...
=DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
=DATE(YEAR(F3+59),MONTH(F3+59)+1,1)
-- David Biddulph "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
It worked perfectly.
Thank you!! "Mike H" wrote: Hi, Try this =IF(DAY(F3+60)=1,F3+60,EOMONTH(F3+60,0)+1) If you get a NAME error then Tools|Addins Check the analysis toolpak and re=enter the formula Mike "NeSchw6G" wrote: I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
You haven't catered for 60 days falling on the 1st of month, you go one too
far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
Hmm! Good point! I am guessing that is why David posted his code with 59
instead of 60. I know you are right and that has to be what the OP meant that he wanted; but, just in case, I thought I would mention that on a close reading of the original posting, my formula does do exactly what the OP asked for. Rick "Bob Phillips" wrote in message ... You haven't catered for 60 days falling on the 1st of month, you go one too far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
I thought I'd given him what he asked for in the bit that said:
" If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. " ? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... Hmm! Good point! I am guessing that is why David posted his code with 59 instead of 60. I know you are right and that has to be what the OP meant that he wanted; but, just in case, I thought I would mention that on a close reading of the original posting, my formula does do exactly what the OP asked for. Rick "Bob Phillips" wrote in message ... You haven't catered for 60 days falling on the 1st of month, you go one too far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
Just my 2 cents:
=EOMONTH(F3+59,0)+1 -- John C "David Biddulph" wrote: I thought I'd given him what he asked for in the bit that said: " If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. " ? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... Hmm! Good point! I am guessing that is why David posted his code with 59 instead of 60. I know you are right and that has to be what the OP meant that he wanted; but, just in case, I thought I would mention that on a close reading of the original posting, my formula does do exactly what the OP asked for. Rick "Bob Phillips" wrote in message ... You haven't catered for 60 days falling on the 1st of month, you go one too far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
Yes, I'd avoided EOMONTH because of the customary saga of Analysis ToolPak
(and I didn't want to have to give an RTFM reply when people don't bother looking at Excel help to find out why the EOMONTH function isn't working for them). :-( -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... Just my 2 cents: =EOMONTH(F3+59,0)+1 -- John C "David Biddulph" wrote: I thought I'd given him what he asked for in the bit that said: " If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. " ? -- David Biddulph "Rick Rothstein (MVP - VB)" wrote in message ... Hmm! Good point! I am guessing that is why David posted his code with 59 instead of 60. I know you are right and that has to be what the OP meant that he wanted; but, just in case, I thought I would mention that on a close reading of the original posting, my formula does do exactly what the OP asked for. Rick "Bob Phillips" wrote in message ... You haven't catered for 60 days falling on the 1st of month, you go one too far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
Well you must be reading a different post from me as the OP said
.... If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. That seems unequivocally clear to me. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... Hmm! Good point! I am guessing that is why David posted his code with 59 instead of 60. I know you are right and that has to be what the OP meant that he wanted; but, just in case, I thought I would mention that on a close reading of the original posting, my formula does do exactly what the OP asked for. Rick "Bob Phillips" wrote in message ... You haven't catered for 60 days falling on the 1st of month, you go one too far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the First of the Month after 60 days
Yes, that section is definitely "unequivocally clear"; and I would have know
that too, had I read the entire message and not just the part that showed in the visible section of the message area of my newsreader.<g The visible part of my displayed message area stopped at this line... "i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08" and the message appeared complete to me, so I never thought to scroll the window down; hence, I never saw the part of the original message you are referring to. That should explain to you what must have seemed like puzzling wording when I wrote this... "I know you are right and that has to be what the OP meant that he wanted..." Rick "Bob Phillips" wrote in message ... Well you must be reading a different post from me as the OP said ... If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. That seems unequivocally clear to me. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... Hmm! Good point! I am guessing that is why David posted his code with 59 instead of 60. I know you are right and that has to be what the OP meant that he wanted; but, just in case, I thought I would mention that on a close reading of the original posting, my formula does do exactly what the OP asked for. Rick "Bob Phillips" wrote in message ... You haven't catered for 60 days falling on the 1st of month, you go one too far then, e.g. 2nd June -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... You can use this formula... =DATE(YEAR(F3+60),MONTH(F3+60)+1,1) Rick "NeSchw6G" wrote in message ... I need help making a formula to use the date in cell F3, add 60 days and then find the first of the following month. i.e. The date given would be 8/21/08 and I would need a formula to produce 11/1/08 If the 60 days falls on the first of the month, then it should produce the same number, as in the date given would be 9/2/08 and 60 days would be 11/1/08 and so it should produce 11/1/08. Thank you, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
Find the number of days in a month | Excel Worksheet Functions | |||
Days per month for calculating storage days | Excel Worksheet Functions | |||
function to fill all days of month to end of month | Excel Worksheet Functions |