5 replies to this topic

[shawn_nee]

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? I found the closest template to what I would like to use, but the ending dates are what I need to change. I have one formula that will auto calculate the end of the month, but I would like it to have just one formula that when the user puts in the 1st of the month the formula knows the end date is the 15th, and when the user puts in 16th the formula go to the end of the month. The second half is done by using =EOMONTH(K6,0) with (K6,0) being the cell with the starting date. Now I am trying to see if I can us the IF function to get the first half of the equation to work for me.

[zamiel]

Perhaps if you attach what you've got so far people might be able to work on it?

[shawn_nee]

Ok, the first formula is

=IF($K$6="","",$K$6+14)

with K6 being the cell the date is in.

I want to add the following formula

=EOMONTH(K6,0)

The first formula just sets the date in cell K7 to the 15th of the month if you type in the 1st.
for example if I put 11/01/2007 in cell K6, K7 will say 11/15/2007.

The second formula sets the date in cell K7 to the end of the month.
For example if I put in 11/16/2007 in K6, K7 will show 11/30/2007
and for 12/16/2007 K7 will show 12/31/2007.

I want to combine the 2 if possible, OR figure out a new formula to use. So for example, if I put in 11/01/2007 the system will read it's the first and automatically put K7 to 11/15/2007, and if I put 11/16/2007 the system will automatically set 11/30/2007 as the end date.

I attached the current spreadsheet I have set up right now.

Attached Files

•  biweekly_time_sheet1.xls   20KB   39 downloads

[shawn_nee]

Ok, I got the answer I was looking for.

Just FYI, the formula was this...

=IF($K$6="","",IF(DAY($K$6)=1,$K$6+14,DATE(YEAR($K$6),MONTH($K$6)+1,0)))

[zamiel]

Sorry Shawn, been flat out and didn't get a chance to look at it

[shawn_nee]

No problems Zamiel, Microsoft has a help forum to get help from other users and I got it there. I have been working on this for about a week, and decided to post here since I knew there were a few guru's like you around here.