Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to find out a solution for a complex formula Weasel Excel Discussion (Misc queries) 4 March 22nd 06 08:41 PM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 09:02 PM
Trending Formula Results by Date sony654 Excel Worksheet Functions 0 January 2nd 06 03:33 AM
Date formula Robyn Bellanger Excel Discussion (Misc queries) 2 December 16th 04 01:41 AM
Need help troubleshooting an array formula XLXP on Win2K KR Excel Worksheet Functions 1 December 13th 04 08:41 PM


All times are GMT +1. The time now is 02:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"