Adding days

M

Mike R

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.
 
B

Biff

Hi!

Try one of these:

This one requires that the Analysis ToolPak be installed:

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

OR

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

Biff
 
R

Ron Rosenfeld

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.
Click to expand...

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


--ron
 
M

Mike R

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.
 
R

Ron Rosenfeld

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
Click to expand...

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
 
M

Mike R

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.
 
R

Roger Govier

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().
 
I

iff

Hi!

It's much easier than that!

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

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

Biff
 
Top