| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Can someone help with a formula to calculate dates?
Can someone help with a formula to calculate ending dates?
For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? |
|
#2
|
|||
|
|||
|
On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP."
wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron |
|
#3
|
|||
|
|||
|
Thank you, I've tried this and it works great. One more question,
is there a way to do Num_Months minus 1 day? "Ron Rosenfeld" wrote: On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP." wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron |
|
#4
|
|||
|
|||
|
=EDATE(Start_date, Num_Months)-1
If I understood correctly. SharonP. wrote: Thank you, I've tried this and it works great. One more question, is there a way to do Num_Months minus 1 day? "Ron Rosenfeld" wrote: On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP." wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron -- Dave Peterson |
|
#5
|
|||
|
|||
|
Thank you, how does it know that the -1 refers to days?
"Dave Peterson" wrote: =EDATE(Start_date, Num_Months)-1 If I understood correctly. SharonP. wrote: Thank you, I've tried this and it works great. One more question, is there a way to do Num_Months minus 1 day? "Ron Rosenfeld" wrote: On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP." wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron -- Dave Peterson |
|
#6
|
|||
|
|||
|
Dates are just numbers to excel.
If you put today's date in a cell and format that cell as General, you'll see a number like: 38572 (for Aug 8, 2005). Excel just counts the number of days from a base date (12/31/1899 for most windows users). So when you subtract 1, you're subtracting one day. In fact, time is a fraction of 1. 1 hour is equal to 1/24. 12 hours is 12/24 (or .5). You can read lots more about how excel treats times (and dates) at Chip Pearson's site: http://www.cpearson.com/excel/datetime.htm SharonP. wrote: Thank you, how does it know that the -1 refers to days? "Dave Peterson" wrote: =EDATE(Start_date, Num_Months)-1 If I understood correctly. SharonP. wrote: Thank you, I've tried this and it works great. One more question, is there a way to do Num_Months minus 1 day? "Ron Rosenfeld" wrote: On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP." wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron -- Dave Peterson -- Dave Peterson |
|
#7
|
|||
|
|||
|
Ron Rosenfeld Wrote: =EDATE(Start_date, Num_Months) --ron I'm getting a #NAME? error with this. Is this correct? =EDATE(8/8/2005,12) Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=393939 |
|
#8
|
|||
|
|||
|
The EDATE function is part of the Analysis Tool Pak add-in. Go to
the Tools menu, choose Add-Ins, then choose "Analysis Tool Pak" from the list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ltat42a" wrote in message ... Ron Rosenfeld Wrote: =EDATE(Start_date, Num_Months) --ron I'm getting a #NAME? error with this. Is this correct? =EDATE(8/8/2005,12) Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=393939 |
|
#9
|
|||
|
|||
|
On Mon, 8 Aug 2005 16:19:10 -0500, Ltat42a
wrote: Ron Rosenfeld Wrote: =EDATE(Start_date, Num_Months) --ron I'm getting a #NAME? error with this. From HELP for EDATE: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. Is this correct? =EDATE(8/8/2005,12) NO. =EDATE("8/8/2005",12) or, less ambiguous: =EDATE(DATE(2005,8,8),12) or put the date in some cell and use the cell reference: A1:= 8/8/2005 B1:= =EDATE(A1,12) --ron |
|
#10
|
|||
|
|||
|
And you'll want to make sure that first parm is a date:
=EDATE(date(2005,8,8),12) Ltat42a wrote: Ron Rosenfeld Wrote: =EDATE(Start_date, Num_Months) --ron I'm getting a #NAME? error with this. Is this correct? =EDATE(8/8/2005,12) Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=393939 -- Dave Peterson |
|
#11
|
|||
|
|||
|
Can you combine the edate function w/ a vlookup? For example I want to take
a date and add XX months but it gets a little complicated in that the XX is actually a choice in a combo box from 12-15. The combo box links to cell D45 and the value of 12 is 1 since it's the first in the list So, I created a lookup table and want to use that if that makes sense. Lookup Month Table 1 12 2 13 3 14 4 15 Right now my formula is - =EDATE(Data!S2, D45) but D45 is 1 instead of 12. Select number of months in renewal period Combo Box (choose from 12 - 15) Results to 1 in the linked cell. "Dave Peterson" wrote: Dates are just numbers to excel. If you put today's date in a cell and format that cell as General, you'll see a number like: 38572 (for Aug 8, 2005). Excel just counts the number of days from a base date (12/31/1899 for most windows users). So when you subtract 1, you're subtracting one day. In fact, time is a fraction of 1. 1 hour is equal to 1/24. 12 hours is 12/24 (or .5). You can read lots more about how excel treats times (and dates) at Chip Pearson's site: http://www.cpearson.com/excel/datetime.htm SharonP. wrote: Thank you, how does it know that the -1 refers to days? "Dave Peterson" wrote: =EDATE(Start_date, Num_Months)-1 If I understood correctly. SharonP. wrote: Thank you, I've tried this and it works great. One more question, is there a way to do Num_Months minus 1 day? "Ron Rosenfeld" wrote: On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP." wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron -- Dave Peterson -- Dave Peterson |
|
#12
|
|||
|
|||
|
=edate(data!s2,vlookup(d45,sheet3!a:b,2,false))
(or something like that--depending on where that table is.) Or maybe: =if(d45="","",edate(data!s2,vlookup(d45,sheet3!a:b ,2,false))) just in case that dropdown (from the forms toolbar) hasn't been used. SharonP. wrote: Can you combine the edate function w/ a vlookup? For example I want to take a date and add XX months but it gets a little complicated in that the XX is actually a choice in a combo box from 12-15. The combo box links to cell D45 and the value of 12 is 1 since it's the first in the list So, I created a lookup table and want to use that if that makes sense. Lookup Month Table 1 12 2 13 3 14 4 15 Right now my formula is - =EDATE(Data!S2, D45) but D45 is 1 instead of 12. Select number of months in renewal period Combo Box (choose from 12 - 15) Results to 1 in the linked cell. "Dave Peterson" wrote: Dates are just numbers to excel. If you put today's date in a cell and format that cell as General, you'll see a number like: 38572 (for Aug 8, 2005). Excel just counts the number of days from a base date (12/31/1899 for most windows users). So when you subtract 1, you're subtracting one day. In fact, time is a fraction of 1. 1 hour is equal to 1/24. 12 hours is 12/24 (or .5). You can read lots more about how excel treats times (and dates) at Chip Pearson's site: http://www.cpearson.com/excel/datetime.htm SharonP. wrote: Thank you, how does it know that the -1 refers to days? "Dave Peterson" wrote: =EDATE(Start_date, Num_Months)-1 If I understood correctly. SharonP. wrote: Thank you, I've tried this and it works great. One more question, is there a way to do Num_Months minus 1 day? "Ron Rosenfeld" wrote: On Mon, 8 Aug 2005 09:56:08 -0700, "SharonP." wrote: Can someone help with a formula to calculate ending dates? For example, I have a beginning date of 01/01/2006 and need to determine an ending date. I need to figure an input variable that has been selected - 12 months or 15 months. ie. if beginning date is 01/01/2006 - it needs to calculate 12 months from that date for an ending date of 12/31/2006. Is this a table and vlookup situation or a formula? =EDATE(Start_date, Num_Months) --ron -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Looking for function or formula to calculate number that is revers | Excel Worksheet Functions | |||
| Need Formula or Function to calculate Margin (reverse of Percent a | Excel Worksheet Functions | |||
| How do I format dates accessed by a formula | Excel Discussion (Misc queries) | |||
| X IN A CELL TO CALCULATE A FORMULA | Excel Worksheet Functions | |||
| Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions | |||
Linear Mode
