Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 74
Default Calculating Dates

Need to a run adhocs on the 1st and 15th of each month to find individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the 59 1/2
age, bascially the 15th adhoc run is to cover the first half of the next
month and the 1st adhoc run to cover the second half of the current month,
covering all dates within that month.

I need to create easy access to the start and end dates for the adhoc, so
I'm attempting to create a locked spreadsheet where the user need only to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not catching all
the days for the next run or repeating days I don't need....any suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!





  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Calculating Dates

Can you rephrase this: who will reach the age of 59 1/2 two weeks prior to
the reaching the 59 1/2
You want to know who will be 59 1/2 in two weeks time
Have a look at DATEDIF in Chip's http://www.cpearson.com/excel/datedif.aspx
This =DATEDIF(A4,TODAY()+14,"m")/12 gives 59.5 with the 1/Aug/1948 DOB in A4

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Need to a run adhocs on the 1st and 15th of each month to find individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the 59
1/2
age, bascially the 15th adhoc run is to cover the first half of the next
month and the 1st adhoc run to cover the second half of the current month,
covering all dates within that month.

I need to create easy access to the start and end dates for the adhoc, so
I'm attempting to create a locked spreadsheet where the user need only to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not catching
all
the days for the next run or repeating days I don't need....any
suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!







  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 74
Default Calculating Dates

My apologies as I didn't explain that very well. Based on the current day's
date, always to be the 1st or the 15th (or the first business day if these
dates fall on a weekend or holiday) I need to determine which start and end
dates to use in an adhoc in order to select all individuals who will turn 59
1/2 within the next two weeks.

For example, assuming today is the 15th of January. I need to determine the
start and end dates based on the current date of 01/15/2008...that is
determine the dates of birth at which the age of 59 1/2 will be reached
between 02/01/2008 and 02/15/2008.




"Bernard Liengme" wrote:

Can you rephrase this: who will reach the age of 59 1/2 two weeks prior to
the reaching the 59 1/2
You want to know who will be 59 1/2 in two weeks time
Have a look at DATEDIF in Chip's http://www.cpearson.com/excel/datedif.aspx
This =DATEDIF(A4,TODAY()+14,"m")/12 gives 59.5 with the 1/Aug/1948 DOB in A4

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Need to a run adhocs on the 1st and 15th of each month to find individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the 59
1/2
age, bascially the 15th adhoc run is to cover the first half of the next
month and the 1st adhoc run to cover the second half of the current month,
covering all dates within that month.

I need to create easy access to the start and end dates for the adhoc, so
I'm attempting to create a locked spreadsheet where the user need only to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not catching
all
the days for the next run or repeating days I don't need....any
suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!








  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Calculating Dates

Here goes!
Test date 15-Jan-08
Start 1-Feb-08
End 15-Feb-08
Bday 1 25-Jul-48
Bday 2 8-Aug-48


I have use dd/mmm/yy since you use US dates and I use the other one. Your
cells will show 01/15/08 ....

B1: start date; value you enter. Depending on weekends/holiday 15 ± a few or
30 ± a few
B2: Start date: we want 1st of next month when test date is 15th or 15th of
next moth when test date is 30th
This is a messy one but seems to work
=IF(ABS(DAY(B1)-15)<4,DATE(YEAR(B1),MONTH(B1)+1,1),
DATE(YEAR(B1),MONTH(B1)+1-1*(DAY(A1)<10),15))
B3: End date = start date +14 =A2+14
B4 Bday 1: =B2-21740 (21740 or more or less 59.5*365.25)
B5 Bday 2: =B3-21740
Note that someone born 25-July-48 will be 59.5 on Start (1-Feb-08), ans one
with bate 8-Aug-48 will be 59.5 on End day 15-Feb-2008 (as checked with
DATEDIF)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
My apologies as I didn't explain that very well. Based on the current
day's
date, always to be the 1st or the 15th (or the first business day if these
dates fall on a weekend or holiday) I need to determine which start and
end
dates to use in an adhoc in order to select all individuals who will turn
59
1/2 within the next two weeks.

For example, assuming today is the 15th of January. I need to determine
the
start and end dates based on the current date of 01/15/2008...that is
determine the dates of birth at which the age of 59 1/2 will be reached
between 02/01/2008 and 02/15/2008.




"Bernard Liengme" wrote:

Can you rephrase this: who will reach the age of 59 1/2 two weeks prior
to
the reaching the 59 1/2
You want to know who will be 59 1/2 in two weeks time
Have a look at DATEDIF in Chip's
http://www.cpearson.com/excel/datedif.aspx
This =DATEDIF(A4,TODAY()+14,"m")/12 gives 59.5 with the 1/Aug/1948 DOB in
A4

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Need to a run adhocs on the 1st and 15th of each month to find
individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the 59
1/2
age, bascially the 15th adhoc run is to cover the first half of the
next
month and the 1st adhoc run to cover the second half of the current
month,
covering all dates within that month.

I need to create easy access to the start and end dates for the adhoc,
so
I'm attempting to create a locked spreadsheet where the user need only
to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not
catching
all
the days for the next run or repeating days I don't need....any
suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!










  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 74
Default Calculating Dates

Works like a charm with one exception, the formula for the end date doesn't
adjust to cover all dates(days) within the test date month, for example...

1/1/08 test date
Start date = 01/15/08
End date = 01/29/08
Bday 1 = 07/08/48
Bday 2 = 07/22/48

1/15/08 test date
Start date = 02/01/08
End date = 02/15/08
Bday 1 = 07/25/08
Bday 2 = 08/08/48

The END DATE days of the 30th and 31st of January are missed and
subsequently, the BDAY days of the 23rd and 24th of February are also missed.
How can the end date be updated to recognize the correct number (based on the
days in the current test date month) to add to B2?

"Bernard Liengme" wrote:

Here goes!
Test date 15-Jan-08
Start 1-Feb-08
End 15-Feb-08
Bday 1 25-Jul-48
Bday 2 8-Aug-48


I have use dd/mmm/yy since you use US dates and I use the other one. Your
cells will show 01/15/08 ....

B1: start date; value you enter. Depending on weekends/holiday 15 ± a few or
30 ± a few
B2: Start date: we want 1st of next month when test date is 15th or 15th of
next moth when test date is 30th
This is a messy one but seems to work
=IF(ABS(DAY(B1)-15)<4,DATE(YEAR(B1),MONTH(B1)+1,1),
DATE(YEAR(B1),MONTH(B1)+1-1*(DAY(A1)<10),15))
B3: End date = start date +14 =A2+14
B4 Bday 1: =B2-21740 (21740 or more or less 59.5*365.25)
B5 Bday 2: =B3-21740
Note that someone born 25-July-48 will be 59.5 on Start (1-Feb-08), ans one
with bate 8-Aug-48 will be 59.5 on End day 15-Feb-2008 (as checked with
DATEDIF)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
My apologies as I didn't explain that very well. Based on the current
day's
date, always to be the 1st or the 15th (or the first business day if these
dates fall on a weekend or holiday) I need to determine which start and
end
dates to use in an adhoc in order to select all individuals who will turn
59
1/2 within the next two weeks.

For example, assuming today is the 15th of January. I need to determine
the
start and end dates based on the current date of 01/15/2008...that is
determine the dates of birth at which the age of 59 1/2 will be reached
between 02/01/2008 and 02/15/2008.




"Bernard Liengme" wrote:

Can you rephrase this: who will reach the age of 59 1/2 two weeks prior
to
the reaching the 59 1/2
You want to know who will be 59 1/2 in two weeks time
Have a look at DATEDIF in Chip's
http://www.cpearson.com/excel/datedif.aspx
This =DATEDIF(A4,TODAY()+14,"m")/12 gives 59.5 with the 1/Aug/1948 DOB in
A4

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Need to a run adhocs on the 1st and 15th of each month to find
individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the 59
1/2
age, bascially the 15th adhoc run is to cover the first half of the
next
month and the 1st adhoc run to cover the second half of the current
month,
covering all dates within that month.

I need to create easy access to the start and end dates for the adhoc,
so
I'm attempting to create a locked spreadsheet where the user need only
to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not
catching
all
the days for the next run or repeating days I don't need....any
suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!













  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Calculating Dates

If A1 holds a date then =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
tells how many days are in that month.
I will let you experiment!
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Works like a charm with one exception, the formula for the end date
doesn't
adjust to cover all dates(days) within the test date month, for example...

1/1/08 test date
Start date = 01/15/08
End date = 01/29/08
Bday 1 = 07/08/48
Bday 2 = 07/22/48

1/15/08 test date
Start date = 02/01/08
End date = 02/15/08
Bday 1 = 07/25/08
Bday 2 = 08/08/48

The END DATE days of the 30th and 31st of January are missed and
subsequently, the BDAY days of the 23rd and 24th of February are also
missed.
How can the end date be updated to recognize the correct number (based on
the
days in the current test date month) to add to B2?

"Bernard Liengme" wrote:

Here goes!
Test date 15-Jan-08
Start 1-Feb-08
End 15-Feb-08
Bday 1 25-Jul-48
Bday 2 8-Aug-48


I have use dd/mmm/yy since you use US dates and I use the other one. Your
cells will show 01/15/08 ....

B1: start date; value you enter. Depending on weekends/holiday 15 ± a few
or
30 ± a few
B2: Start date: we want 1st of next month when test date is 15th or 15th
of
next moth when test date is 30th
This is a messy one but seems to work
=IF(ABS(DAY(B1)-15)<4,DATE(YEAR(B1),MONTH(B1)+1,1),
DATE(YEAR(B1),MONTH(B1)+1-1*(DAY(A1)<10),15))
B3: End date = start date +14 =A2+14
B4 Bday 1: =B2-21740 (21740 or more or less 59.5*365.25)
B5 Bday 2: =B3-21740
Note that someone born 25-July-48 will be 59.5 on Start (1-Feb-08), ans
one
with bate 8-Aug-48 will be 59.5 on End day 15-Feb-2008 (as checked with
DATEDIF)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
My apologies as I didn't explain that very well. Based on the current
day's
date, always to be the 1st or the 15th (or the first business day if
these
dates fall on a weekend or holiday) I need to determine which start and
end
dates to use in an adhoc in order to select all individuals who will
turn
59
1/2 within the next two weeks.

For example, assuming today is the 15th of January. I need to determine
the
start and end dates based on the current date of 01/15/2008...that is
determine the dates of birth at which the age of 59 1/2 will be reached
between 02/01/2008 and 02/15/2008.




"Bernard Liengme" wrote:

Can you rephrase this: who will reach the age of 59 1/2 two weeks
prior
to
the reaching the 59 1/2
You want to know who will be 59 1/2 in two weeks time
Have a look at DATEDIF in Chip's
http://www.cpearson.com/excel/datedif.aspx
This =DATEDIF(A4,TODAY()+14,"m")/12 gives 59.5 with the 1/Aug/1948 DOB
in
A4

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Need to a run adhocs on the 1st and 15th of each month to find
individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the
59
1/2
age, bascially the 15th adhoc run is to cover the first half of the
next
month and the 1st adhoc run to cover the second half of the current
month,
covering all dates within that month.

I need to create easy access to the start and end dates for the
adhoc,
so
I'm attempting to create a locked spreadsheet where the user need
only
to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not
catching
all
the days for the next run or repeating days I don't need....any
suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!













  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 74
Default Calculating Dates

It took me awhile but it works like a charm now!!

I kept what you gave me for the Start Date, Bday1 and Bday2. For the End
Date I play around with your formula for the Start Date, adding in the days
of the month formula you gave me and came up with the following...
=IF(ABS(DAY(B2))=14,DATE(YEAR(B2),MONTH(B2),DAY(DA TE(YEAR(B2),MONTH(B2)+1,0))),DATE(YEAR(B2),MONTH(B 2),DAY(13)))

So, if I enter a test date of 1/1/08, I get
Test Date 01/01/08
Start Date 01/14/08
End Date 01/31/08
Bday 1 07/07/48
Bday 2 07/24/48

If I enter a test date of 1/15/08, I get
Test Date 01/15/08
Start Date 02/01/08
End Date 02/13/08
Bday 1 07/25/48
Bday 2 08/06/48

It covers every day of each month for both the Start/End dates as well as
all birthdays witin those Start/End dates . Thanks again!!



"Bernard Liengme" wrote:

If A1 holds a date then =DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
tells how many days are in that month.
I will let you experiment!
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Works like a charm with one exception, the formula for the end date
doesn't
adjust to cover all dates(days) within the test date month, for example...

1/1/08 test date
Start date = 01/15/08
End date = 01/29/08
Bday 1 = 07/08/48
Bday 2 = 07/22/48

1/15/08 test date
Start date = 02/01/08
End date = 02/15/08
Bday 1 = 07/25/08
Bday 2 = 08/08/48

The END DATE days of the 30th and 31st of January are missed and
subsequently, the BDAY days of the 23rd and 24th of February are also
missed.
How can the end date be updated to recognize the correct number (based on
the
days in the current test date month) to add to B2?

"Bernard Liengme" wrote:

Here goes!
Test date 15-Jan-08
Start 1-Feb-08
End 15-Feb-08
Bday 1 25-Jul-48
Bday 2 8-Aug-48


I have use dd/mmm/yy since you use US dates and I use the other one. Your
cells will show 01/15/08 ....

B1: start date; value you enter. Depending on weekends/holiday 15 ± a few
or
30 ± a few
B2: Start date: we want 1st of next month when test date is 15th or 15th
of
next moth when test date is 30th
This is a messy one but seems to work
=IF(ABS(DAY(B1)-15)<4,DATE(YEAR(B1),MONTH(B1)+1,1),
DATE(YEAR(B1),MONTH(B1)+1-1*(DAY(A1)<10),15))
B3: End date = start date +14 =A2+14
B4 Bday 1: =B2-21740 (21740 or more or less 59.5*365.25)
B5 Bday 2: =B3-21740
Note that someone born 25-July-48 will be 59.5 on Start (1-Feb-08), ans
one
with bate 8-Aug-48 will be 59.5 on End day 15-Feb-2008 (as checked with
DATEDIF)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
My apologies as I didn't explain that very well. Based on the current
day's
date, always to be the 1st or the 15th (or the first business day if
these
dates fall on a weekend or holiday) I need to determine which start and
end
dates to use in an adhoc in order to select all individuals who will
turn
59
1/2 within the next two weeks.

For example, assuming today is the 15th of January. I need to determine
the
start and end dates based on the current date of 01/15/2008...that is
determine the dates of birth at which the age of 59 1/2 will be reached
between 02/01/2008 and 02/15/2008.




"Bernard Liengme" wrote:

Can you rephrase this: who will reach the age of 59 1/2 two weeks
prior
to
the reaching the 59 1/2
You want to know who will be 59 1/2 in two weeks time
Have a look at DATEDIF in Chip's
http://www.cpearson.com/excel/datedif.aspx
This =DATEDIF(A4,TODAY()+14,"m")/12 gives 59.5 with the 1/Aug/1948 DOB
in
A4

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Anne" wrote in message
...
Need to a run adhocs on the 1st and 15th of each month to find
individuals
who will reach the age of 59 1/2 two weeks prior to the reaching the
59
1/2
age, bascially the 15th adhoc run is to cover the first half of the
next
month and the 1st adhoc run to cover the second half of the current
month,
covering all dates within that month.

I need to create easy access to the start and end dates for the
adhoc,
so
I'm attempting to create a locked spreadsheet where the user need
only
to
enter the current date. I am trying to use the EDATE function,
=EDATE(A2,-715)-14, A2 being the current date, however I am not
catching
all
the days for the next run or repeating days I don't need....any
suggestions?

A2
2/15/2008 Current Date

A4
8/1/1948 DOB for 59 1/2 (Adhoc Start date)

A5
?/??/???? Adhoc End Date

Thanks in advance!!














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
calculating due dates Ndstc1 Excel Discussion (Misc queries) 2 November 8th 07 09:16 PM
Calculating with dates Henk Excel Discussion (Misc queries) 3 June 13th 07 04:24 PM
Calculating dates Toby0320 Excel Worksheet Functions 11 July 18th 06 04:52 PM
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
I need help calculating dates? jeda67 Excel Worksheet Functions 4 August 30th 05 02:14 PM


All times are GMT +1. The time now is 06:36 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"