| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto calculating the month end
I am trying to work on a template for a bi-weekly employee time sheet. I need
the dates to range from the 1st to the 15th, and then from the 16th till the end of the month. Is there a formula that can be used so the system would recognize the months that have 30 or 31 days? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
if you install the analysis toolpak, you can use the eomonth function.
-- Gary "Rip1877" wrote in message ... I am trying to work on a template for a bi-weekly employee time sheet. I need the dates to range from the 1st to the 15th, and then from the 16th till the end of the month. Is there a formula that can be used so the system would recognize the months that have 30 or 31 days? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
Rip1877,
Try the function =Date(year,month,day) "Rip1877" wrote: I am trying to work on a template for a bi-weekly employee time sheet. I need the dates to range from the 1st to the 15th, and then from the 16th till the end of the month. Is there a formula that can be used so the system would recognize the months that have 30 or 31 days? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
My preference is to use the first of the next month minus one. Something like
this... =DATE(2007, 10, 1) - 1 Placed in a cell gives you Sept 30th... -- HTH... Jim Thomlinson "Rip1877" wrote: I am trying to work on a template for a bi-weekly employee time sheet. I need the dates to range from the 1st to the 15th, and then from the 16th till the end of the month. Is there a formula that can be used so the system would recognize the months that have 30 or 31 days? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
Ok, I tried looking for the eomonth function. Is this an add-on? I have
Office 2003. Would I need 2007 for this function? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
Or even
=date(2007,10,0) The zeroeth day of the next month is the last day of the previous month. Jim Thomlinson wrote: My preference is to use the first of the next month minus one. Something like this... =DATE(2007, 10, 1) - 1 Placed in a cell gives you Sept 30th... -- HTH... Jim Thomlinson "Rip1877" wrote: I am trying to work on a template for a bi-weekly employee time sheet. I need the dates to range from the 1st to the 15th, and then from the 16th till the end of the month. Is there a formula that can be used so the system would recognize the months that have 30 or 31 days? -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
go to tools/addins and check analysis toolpak and install it if it isn't already
installed. you will probably need the o2k3 cd. with a date in a1 enter in b1 =eomonth(a1,0) will give you the last day of the month in cell a1 -- Gary "Rip1877" wrote in message ... Ok, I tried looking for the eomonth function. Is this an add-on? I have Office 2003. Would I need 2007 for this function? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
I tried to explain that to someone in the office a while back and they just
didn't quite click in to it. I changed my explanation to the first minus 1 and the light bulb came on. Personally I use the 0 thing but I have never tried to explain it since. I guess the 0th day of the month was a bit too conceptual... -- HTH... Jim Thomlinson "Dave Peterson" wrote: Or even =date(2007,10,0) The zeroeth day of the next month is the last day of the previous month. Jim Thomlinson wrote: My preference is to use the first of the next month minus one. Something like this... =DATE(2007, 10, 1) - 1 Placed in a cell gives you Sept 30th... -- HTH... Jim Thomlinson "Rip1877" wrote: I am trying to work on a template for a bi-weekly employee time sheet. I need the dates to range from the 1st to the 15th, and then from the 16th till the end of the month. Is there a formula that can be used so the system would recognize the months that have 30 or 31 days? -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Auto calculating the month end
Can I set it up as an IF function? Like if the date<=15 then it uses the
15th, IFdate=16 then =eomonth(a1,0) ? What I want to do is set it up so it will input the end of a pay period as either the 15th or the end of the month. depending on if it is the first or second pay period. Can I use the IF function for that and if so, what would the formula need to look like? "Gary Keramidas" wrote: go to tools/addins and check analysis toolpak and install it if it isn't already installed. you will probably need the o2k3 cd. with a date in a1 enter in b1 =eomonth(a1,0) will give you the last day of the month in cell a1 -- Gary |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating by month | Excel Worksheet Functions | |||
| Calculating the end of the month | Excel Programming | |||
| Calculating Month Name | Excel Discussion (Misc queries) | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
| Calculating Last day of month | Excel Programming | |||
Linear Mode
