Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
I have column A with date (mm,dd,yyy) I would like excel to
calculate....the first day of the month following 3 months after the date entered. In other words, date entered is 10/07/2007, excel should return February 1, 2008. 3 full months would be January 7, 2007 (or8th) and the answer returned should be the 1st of the next month after 3 full months. Thank you all so much for all of your help on my numerous posts! This is a fabulous group of professionals always willing to help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
On Oct 7, 8:17 pm, wx4usa wrote:
I have column A with date (mm,dd,yyy) I would like excel to calculate....the first day of the month following 3 months after the date entered. In other words, date entered is 10/07/2007, excel should return February 1, 2008. 3 full months would be January 7, 2007 (or8th) and the answer returned should be the 1st of the next month after 3 full months. Thank you all so much for all of your help on my numerous posts! This is a fabulous group of professionals always willing to help! Actually, better stated would be rounded to the beginning of the month following 90 full days of the date on column A. For Example, an employee would be eligible for medical coverage on the 1st of the month after 90 days of employment |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
On Mon, 08 Oct 2007 01:23:01 -0000, wx4usa wrote:
On Oct 7, 8:17 pm, wx4usa wrote: I have column A with date (mm,dd,yyy) I would like excel to calculate....the first day of the month following 3 months after the date entered. In other words, date entered is 10/07/2007, excel should return February 1, 2008. 3 full months would be January 7, 2007 (or8th) and the answer returned should be the 1st of the next month after 3 full months. Thank you all so much for all of your help on my numerous posts! This is a fabulous group of professionals always willing to help! Actually, better stated would be rounded to the beginning of the month following 90 full days of the date on column A. For Example, an employee would be eligible for medical coverage on the 1st of the month after 90 days of employment =A1+123-DAY(A1+90)-DAY(A1+122-DAY(A1+90)) or, perhaps easier to understand: =DATE(YEAR(A1+90),MONTH(A1+90)+1,1) The above formulas are very literal to your specifications. In other words, if the 90th day of employment is 5/1, the date will be rounded up to 6/1. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
Try this
=DATE(YEAR(A1),MONTH(A1)+3,1) "Ron Rosenfeld" wrote: On Mon, 08 Oct 2007 01:23:01 -0000, wx4usa wrote: On Oct 7, 8:17 pm, wx4usa wrote: I have column A with date (mm,dd,yyy) I would like excel to calculate....the first day of the month following 3 months after the date entered. In other words, date entered is 10/07/2007, excel should return February 1, 2008. 3 full months would be January 7, 2007 (or8th) and the answer returned should be the 1st of the next month after 3 full months. Thank you all so much for all of your help on my numerous posts! This is a fabulous group of professionals always willing to help! Actually, better stated would be rounded to the beginning of the month following 90 full days of the date on column A. For Example, an employee would be eligible for medical coverage on the 1st of the month after 90 days of employment =A1+123-DAY(A1+90)-DAY(A1+122-DAY(A1+90)) or, perhaps easier to understand: =DATE(YEAR(A1+90),MONTH(A1+90)+1,1) The above formulas are very literal to your specifications. In other words, if the 90th day of employment is 5/1, the date will be rounded up to 6/1. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
Try this:
=DATE(YEAR(A1+90),MONTH(A1+90)+1,0)+1 -- Biff Microsoft Excel MVP "wx4usa" wrote in message oups.com... I have column A with date (mm,dd,yyy) I would like excel to calculate....the first day of the month following 3 months after the date entered. In other words, date entered is 10/07/2007, excel should return February 1, 2008. 3 full months would be January 7, 2007 (or8th) and the answer returned should be the 1st of the next month after 3 full months. Thank you all so much for all of your help on my numerous posts! This is a fabulous group of professionals always willing to help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
On Sun, 7 Oct 2007 18:46:00 -0700, Mike wrote:
Try this =DATE(YEAR(A1),MONTH(A1)+3,1) Did you try this solution? Aside from the fact that the OP changed his original request from 3 months to 90 days, your solution rounds down and doesn't work on the example the OP gave. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time and Date Question
My mistake I misread the OP post
Thanks for pointing it out "Ron Rosenfeld" wrote: On Sun, 7 Oct 2007 18:46:00 -0700, Mike wrote: Try this =DATE(YEAR(A1),MONTH(A1)+3,1) Did you try this solution? Aside from the fact that the OP changed his original request from 3 months to 90 days, your solution rounds down and doesn't work on the example the OP gave. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date time format question please | Excel Discussion (Misc queries) | |||
Microsoft Date and Time Picker Control 6.0 (SP6) question | Excel Discussion (Misc queries) | |||
Another question about time and date | Excel Discussion (Misc queries) | |||
question about entering date and time | Excel Discussion (Misc queries) | |||
Date/Time Question | Excel Worksheet Functions |