Adding days
 

What formula would I use in H6 to have that results be:
Jan 15 2005 if Jan 1 2005 is entered into C6 and
Jan 31 2005 if Jan 16 i2005 is entered into C6?

I am trying to put together an employee time sheet that shows paydays from
the 1st to the 15th and from the 16th to the end of the month.

xl2k
Thanks
Mike R.

Hi!

Try one of these:

This one requires that the Analysis ToolPak be installed:

=IF(DAY(C6)<=15,DATE(YEAR(C6),MONTH(C6),15),EOMONT H(C6,0))

OR

=IF(DAY(C6)<=15,DATE(YEAR(C6),MONTH(C6),15),DATE(Y EAR
(C6),MONTH(C6)+1,0))

Biff

-----Original Message-----
What formula would I use in H6 to have that results be:
Jan 15 2005 if Jan 1 2005 is entered into C6 and
Jan 31 2005 if Jan 16 i2005 is entered into C6?

I am trying to put together an employee time sheet that
shows paydays from
the 1st to the 15th and from the 16th to the end of the
month.

xl2k
Thanks
Mike R.

.

On Fri, 18 Feb 2005 19:51:01 -0800, Mike R
wrote:

What formula would I use in H6 to have that results be:
Jan 15 2005 if Jan 1 2005 is entered into C6 and
Jan 31 2005 if Jan 16 i2005 is entered into C6?

I am trying to put together an employee time sheet that shows paydays from
the 1st to the 15th and from the 16th to the end of the month.

xl2k
Thanks
Mike R.

=IF(DAY(C6)15,C6+16-DAY(C6+16),C6-DAY(C6)+15)


--ron

Thanks for the responses, got that part working. One other question. I
would like to put a drop down with only those two date options for input.
How do I do that so the date options within the dropdown automatically update
after each selection. I can enter all the dates in a list and do data
validation but was hoping there was something not so ugly!!

Thanks again

Mike R.

"Ron Rosenfeld" wrote:

On Fri, 18 Feb 2005 19:51:01 -0800, Mike R
wrote:

What formula would I use in H6 to have that results be:
Jan 15 2005 if Jan 1 2005 is entered into C6 and
Jan 31 2005 if Jan 16 i2005 is entered into C6?

I am trying to put together an employee time sheet that shows paydays from
the 1st to the 15th and from the 16th to the end of the month.

xl2k
Thanks
Mike R.

=IF(DAY(C6)15,C6+16-DAY(C6+16),C6-DAY(C6)+15)


--ron

On Sat, 19 Feb 2005 15:17:06 -0800, Mike R
wrote:

Thanks for the responses, got that part working. One other question. I
would like to put a drop down with only those two date options for input.
How do I do that so the date options within the dropdown automatically update
after each selection. I can enter all the dates in a list and do data
validation but was hoping there was something not so ugly!!

Thanks again

I'm not sure exactly what you want for valid dates to be allowed under what
circumstances. Examples might make it more clear to me.

However, you could certainly have a list with two dates, and have those dates
be calculated by formulas depending on the contents of H6. So each time you
made an entry into C6, the subsequent allowable entries, which are based on the
calculated value in H6, would change.



--ron

Thanks for the post back, I do appreciate it.
What I am trying to set up: the user selects a date from a drop down for
the begin date of the pay period, ie jan.1, 2005. then after the end of this
pay period (15th of the month) they would be able to select the next pay
period that begins on the 16th. I would like to have this available for each
subsequent pay period. Another way of putting it: To have a selection of
only two possible date, the first of each month and the 16th of each month,
that would progress through the year. Kind of rolling from one month to the
next. Kind of like a ()NOW function that would only show the first and the
16th. Hope this helps, and thank you again for the help!!

Mike R.
"Ron Rosenfeld" wrote:

On Sat, 19 Feb 2005 15:17:06 -0800, Mike R
wrote:

Thanks for the responses, got that part working. One other question. I
would like to put a drop down with only those two date options for input.
How do I do that so the date options within the dropdown automatically update
after each selection. I can enter all the dates in a list and do data
validation but was hoping there was something not so ugly!!

Thanks again

I'm not sure exactly what you want for valid dates to be allowed under what
circumstances. Examples might make it more clear to me.

However, you could certainly have a list with two dates, and have those dates
be calculated by formulas depending on the contents of H6. So each time you
made an entry into C6, the subsequent allowable entries, which are based on the
calculated value in H6, would change.



--ron

Hi Mike

One way would be as follows.
I happened to use cell B12 to hold the value =TODAY()
Then in B13
=IF(--LEFT(TEXT(B12,"dd/mm/yy"),2)<16,DATE(YEAR(B12),MONTH(B12),16),DATE(YEAR (B12),MONTH(B12)+1,1))
and in cell B14
=IF(--LEFT(TEXT(B12,"dd/mm/yy"),2)=16,DATE(YEAR(B12),MONTH(B12)+1,16),DATE(Y EAR(B12),MONTH(B12)+1,1))

Use B13:B14 as your list range for the drop down.

You can substitute Today() for B12 in the formulae and bypass having
=TODAY() in cell B12, but I used the intermediate cell in order to test out
different dates before inserting =TODAY().

--
Regards
Roger Govier
"Mike R" wrote in message
...
Thanks for the post back, I do appreciate it.
What I am trying to set up: the user selects a date from a drop down for
the begin date of the pay period, ie jan.1, 2005. then after the end of
this
pay period (15th of the month) they would be able to select the next pay
period that begins on the 16th. I would like to have this available for
each
subsequent pay period. Another way of putting it: To have a selection of
only two possible date, the first of each month and the 16th of each
month,
that would progress through the year. Kind of rolling from one month to
the
next. Kind of like a ()NOW function that would only show the first and
the
16th. Hope this helps, and thank you again for the help!!

Mike R.
"Ron Rosenfeld" wrote:

On Sat, 19 Feb 2005 15:17:06 -0800, Mike R

wrote:

Thanks for the responses, got that part working. One other question. I
would like to put a drop down with only those two date options for
input.
How do I do that so the date options within the dropdown automatically
update
after each selection. I can enter all the dates in a list and do data
validation but was hoping there was something not so ugly!!

Thanks again

I'm not sure exactly what you want for valid dates to be allowed under
what
circumstances. Examples might make it more clear to me.

However, you could certainly have a list with two dates, and have those
dates
be calculated by formulas depending on the contents of H6. So each time
you
made an entry into C6, the subsequent allowable entries, which are based
on the
calculated value in H6, would change.



--ron

Ron, Biff, and Roger.....Thanks again for all the help you guys are great!!!

"Roger Govier" wrote:

Hi Mike

One way would be as follows.
I happened to use cell B12 to hold the value =TODAY()
Then in B13
=IF(--LEFT(TEXT(B12,"dd/mm/yy"),2)<16,DATE(YEAR(B12),MONTH(B12),16),DATE(YEAR (B12),MONTH(B12)+1,1))
and in cell B14
=IF(--LEFT(TEXT(B12,"dd/mm/yy"),2)=16,DATE(YEAR(B12),MONTH(B12)+1,16),DATE(Y EAR(B12),MONTH(B12)+1,1))

Use B13:B14 as your list range for the drop down.

You can substitute Today() for B12 in the formulae and bypass having
=TODAY() in cell B12, but I used the intermediate cell in order to test out
different dates before inserting =TODAY().

--
Regards
Roger Govier
"Mike R" wrote in message
...
Thanks for the post back, I do appreciate it.
What I am trying to set up: the user selects a date from a drop down for
the begin date of the pay period, ie jan.1, 2005. then after the end of
this
pay period (15th of the month) they would be able to select the next pay
period that begins on the 16th. I would like to have this available for
each
subsequent pay period. Another way of putting it: To have a selection of
only two possible date, the first of each month and the 16th of each
month,
that would progress through the year. Kind of rolling from one month to
the
next. Kind of like a ()NOW function that would only show the first and
the
16th. Hope this helps, and thank you again for the help!!

Mike R.
"Ron Rosenfeld" wrote:

On Sat, 19 Feb 2005 15:17:06 -0800, Mike R

wrote:

Thanks for the responses, got that part working. One other question. I
would like to put a drop down with only those two date options for
input.
How do I do that so the date options within the dropdown automatically
update
after each selection. I can enter all the dates in a list and do data
validation but was hoping there was something not so ugly!!

Thanks again

I'm not sure exactly what you want for valid dates to be allowed under
what
circumstances. Examples might make it more clear to me.

However, you could certainly have a list with two dates, and have those
dates
be calculated by formulas depending on the contents of H6. So each time
you
made an entry into C6, the subsequent allowable entries, which are based
on the
calculated value in H6, would change.



--ron

Hi!

It's much easier than that!

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

=DATE(YEAR(TODAY()),MONTH(TODAY()),16)

Biff

-----Original Message-----
Hi Mike

One way would be as follows.
I happened to use cell B12 to hold the value =TODAY()
Then in B13
=IF(--LEFT(TEXT(B12,"dd/mm/yy"),2)<16,DATE(YEAR(B12),MONTH
(B12),16),DATE(YEAR(B12),MONTH(B12)+1,1))
and in cell B14
=IF(--LEFT(TEXT(B12,"dd/mm/yy"),2)=16,DATE(YEAR
(B12),MONTH(B12)+1,16),DATE(YEAR(B12),MONTH(B12)+1 ,1))

Use B13:B14 as your list range for the drop down.

You can substitute Today() for B12 in the formulae and
bypass having
=TODAY() in cell B12, but I used the intermediate cell in
order to test out
different dates before inserting =TODAY().

--
Regards
Roger Govier
"Mike R" wrote in
message
...
Thanks for the post back, I do appreciate it.
What I am trying to set up: the user selects a date
from a drop down for
the begin date of the pay period, ie jan.1, 2005. then
after the end of
this
pay period (15th of the month) they would be able to
select the next pay
period that begins on the 16th. I would like to have
this available for
each
subsequent pay period. Another way of putting it: To
have a selection of
only two possible date, the first of each month and the
16th of each
month,
that would progress through the year. Kind of rolling
from one month to
the
next. Kind of like a ()NOW function that would only
show the first and
the
16th. Hope this helps, and thank you again for the
help!!

Mike R.
"Ron Rosenfeld" wrote:

On Sat, 19 Feb 2005 15:17:06 -0800, Mike R

wrote:

Thanks for the responses, got that part working. One
other question. I
would like to put a drop down with only those two
date options for
input.
How do I do that so the date options within the
dropdown automatically
update
after each selection. I can enter all the dates in a
list and do data
validation but was hoping there was something not so
ugly!!

Thanks again

I'm not sure exactly what you want for valid dates to
be allowed under
what
circumstances. Examples might make it more clear to
me.

However, you could certainly have a list with two
dates, and have those
dates
be calculated by formulas depending on the contents of
H6. So each time
you
made an entry into C6, the subsequent allowable
entries, which are based
on the
calculated value in H6, would change.



--ron



.