Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
I am trying to figure out the due date for new a test, and then copy that
formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
=A1+180
"mark.nelson4" wrote: I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
Surely you must mean
=DATE(2006,10,B165+13) otherwise you add 180 months? Or do you mean you want to add 6 months each time? =DATE(2006,10+6*ROWS($A$1:A1),13) Anyway, if you mean you want the date to add 180 days each time it is copied down? =DATE(2006,10,13+180*ROWS($A$1:A1)) Regards, Peo Sjoblom mark.nelson4 wrote: I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
Why not just use the cell that contains the date plus 180? eg, A1 contains
13th Oct 2006, so =A1+180 and format as a date. You would have issues in this where a test would be scheduled for a Saturday or Sunday, maybe a Bank Holiday too. These can be overcome using the WEEKDAY function. Regards, Alan. "mark.nelson4" wrote in message ... I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
Hi
With base date in A1, to calculate a date 180 days later: =A1+180 Arvi Laanemets "mark.nelson4" wrote in message ... I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
If the date of the test is there, just add the 180 days to it. Assumed layout:
A B C 1 Names Tested Dates Next Due Dates So if B165 has the Tested Date in it, then in C165: =B165+180 You could get fancy and check to see if that day is Sat/Sun and move the due date to Friday before last possible date due: =IF(WEEKDAY(B165+180)=1,B165+178,IF(WEEKDAY(B165+1 80)=7,B165+179,B165+180)) "mark.nelson4" wrote: I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
If you wanted to ensure that the date returned was on a weekday (gives the
previous workday if resulting date is a weekend) =WORKDAY(A165+B165+1,-1) where A165 contains date and B165 contains number of days to add or without using Analysis ToolPak functions =A165+B165-MAX(0,WEEKDAY(A165+B165,2)-5) "JLatham" wrote: If the date of the test is there, just add the 180 days to it. Assumed layout: A B C 1 Names Tested Dates Next Due Dates So if B165 has the Tested Date in it, then in C165: =B165+180 You could get fancy and check to see if that day is Sat/Sun and move the due date to Friday before last possible date due: =IF(WEEKDAY(B165+180)=1,B165+178,IF(WEEKDAY(B165+1 80)=7,B165+179,B165+180)) "mark.nelson4" wrote: I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Functions
Good point! I didn't even think about WORKDAY(). Much more concise.
"daddylonglegs" wrote: If you wanted to ensure that the date returned was on a weekday (gives the previous workday if resulting date is a weekend) =WORKDAY(A165+B165+1,-1) where A165 contains date and B165 contains number of days to add or without using Analysis ToolPak functions =A165+B165-MAX(0,WEEKDAY(A165+B165,2)-5) "JLatham" wrote: If the date of the test is there, just add the 180 days to it. Assumed layout: A B C 1 Names Tested Dates Next Due Dates So if B165 has the Tested Date in it, then in C165: =B165+180 You could get fancy and check to see if that day is Sat/Sun and move the due date to Friday before last possible date due: =IF(WEEKDAY(B165+180)=1,B165+178,IF(WEEKDAY(B165+1 80)=7,B165+179,B165+180)) "mark.nelson4" wrote: I am trying to figure out the due date for new a test, and then copy that formula straight down the worksheet. For example Person A took her inital test on 13 Oct 07, 180 days later the next test is due on 13 Apr 07. Now I entered the formula =DATE(2006,10+B165[180 days],13) to give me the next test date due on 13 Apr 07. However when I go down to the next row to complete the next test date for person B who took his test initally on a different date: 23 Feb 06, I began to realize that I would have to individually change the intial test date in the same formula for each individual person. I have over two hundred people! My question is this? Is there any way that excel could calculate the next test date due with the same formula without me having to go into that same formula on each row and change the intial test date of each person? Thank you for your help, I look forward to a response from you soon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
IF and Date Functions | Excel Worksheet Functions | |||
Advanced Date Functions | Excel Worksheet Functions | |||
Date functions | New Users to Excel | |||
search for latest date | Excel Worksheet Functions |