Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I calculate the days between dates? | Excel Discussion (Misc queries) | |||
HOW TO CALCULATE THE DAYS? | Excel Worksheet Functions | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions | |||
Adding column data | Excel Worksheet Functions |