Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Date Formula
Working on a sheet that I need date calculations formulas. 1.) Need it to calculate the difference between two dates. (Pretty much have this one nailed) 2.) Once the calculation is made I need for it to give me a value back based on the difference between the two dates. Ex. Vacation accrues over time, let's say every six months you get six vacation days. I want to put a hiring date into one cell (6/1/05) and then have the calculation refer back to that cell to get the result. So based on the hire date (6/1/05) and the current date (7/18/06) I would get a result of 12 vacation days. 3.) I also need for thier to be a ceiling or a limit you could call it. So that it cannot go over maybe fifteen days. I am currently working on it now but just thought I would post it and see what other options are out there. Thanks in advance guys and gals. :) -- kermitforney ------------------------------------------------------------------------ kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536 View this thread: http://www.excelforum.com/showthread...hreadid=562681 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Date Formula
Why wouldn't it be 13 days?
6/1/05 to 7/18/06 is 13 months plus! Or, are you accruing vacation time in 6 day (month) increments? If so, then where is the break point to arrive at your max of 15 days? Need some more info. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "kermitforney" wrote in message news:kermitforney.2b5zwj_1153265711.4561@excelforu m-nospam.com... Working on a sheet that I need date calculations formulas. 1.) Need it to calculate the difference between two dates. (Pretty much have this one nailed) 2.) Once the calculation is made I need for it to give me a value back based on the difference between the two dates. Ex. Vacation accrues over time, let's say every six months you get six vacation days. I want to put a hiring date into one cell (6/1/05) and then have the calculation refer back to that cell to get the result. So based on the hire date (6/1/05) and the current date (7/18/06) I would get a result of 12 vacation days. 3.) I also need for thier to be a ceiling or a limit you could call it. So that it cannot go over maybe fifteen days. I am currently working on it now but just thought I would post it and see what other options are out there. Thanks in advance guys and gals. :) -- kermitforney ------------------------------------------------------------------------ kermitforney's Profile: http://www.excelforum.com/member.php...o&userid=32536 View this thread: http://www.excelforum.com/showthread...hreadid=562681 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Date Formula
On Tue, 18 Jul 2006 19:32:42 -0400, kermitforney
wrote: Working on a sheet that I need date calculations formulas. 1.) Need it to calculate the difference between two dates. (Pretty much have this one nailed) 2.) Once the calculation is made I need for it to give me a value back based on the difference between the two dates. Ex. Vacation accrues over time, let's say every six months you get six vacation days. I want to put a hiring date into one cell (6/1/05) and then have the calculation refer back to that cell to get the result. So based on the hire date (6/1/05) and the current date (7/18/06) I would get a result of 12 vacation days. 3.) I also need for thier to be a ceiling or a limit you could call it. So that it cannot go over maybe fifteen days. I am currently working on it now but just thought I would post it and see what other options are out there. Thanks in advance guys and gals. :) =MIN(15,INT(DATEDIF(HireDate,CurrentDate,"m")/6)*6) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to find out a solution for a complex formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Trending Formula Results by Date | Excel Worksheet Functions | |||
Date formula | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |