Formulas, employee eval date &....

[noli414]

I need help with two formulas,
1st one, A1 = 07/01/08. If A1 falls between the 1st and the 15th of the month, the desired result would be A2="August". Would need the next month for any date between 01 and 15.

If A1 falls between the 16th and the 31st, the desired result would be 60 days out. i.e. if A1 = 07/16/08, A2 = 09/14/08, if A1 = 07/31/08, A2 = 09/29/08.

2nd formula, I have a Employee Schedule template that calculates hours. My only problem is putting text such as OFF, VACATION, etc.. in the cells where it is formatted to have times such as 10:00 AM to 2:00 PM. The formula that calculates the overall hours will not work if text is in these cells. How do I get it to ignore the text? I know it's possible, I've seen it in other ssheets, but have had no success myself.

Any help with either formula would be greatly appreciated!

[Richard Buttrey]

Hi, and welcome to the forum.

No 1.
A2: =IF(DAY(A1)<=15,A1+32,A1+64)
formatted as "mmmm"

No 2.
Assuming you've got Start & Finish times in A1:B4, with C1:C4 being the difference between the two times (formatted as a number). Then use the formula

=SUMIF(C1:C4,">1",C1:C4)

cells in C1:C4 with error values will not affect the calculation.

HTH

[daddylonglegs]

For the first formula it looks like you want to return just a month if the day is 1-15, otherwise add 60 days? If so try

=IF(DAY(A1)<16,TEXT(A1+31,"mmmm"),A1+60)

For the second, assuming start time is in A1 and end time in B1 then to calculate the difference but allow text

=IF(COUNT(A1,B1)=2,MOD(B1-A1,1),"")

[noli414]

Thank you for the help! The first one worked perfectly, it's the schedule that gets me confused. Currently using

B4=10:00 AM C4=6:00 PM D4= 8.0 Hours
B5=12:00 PM C5= 1:00 PM D5= 1.0 Hours

=(C4+(C4<B4)-B4)*24 to calc hours for each day.

So If put B4 = "Off" and "blank" out C4, it would ruin the calculation for the day and the week.

I use the formula below to calculate the weekly total of hours. Can this be shortened? and yes, it's backwards.
=SUM(V4-V5+S4-S5+P4-P5+M4-M5+J4-J5+G4-G5+D4-D5)

I am also trying to link worksheets and noticed that if the worksheet I am linking from is blank, but is formatted for time 1:30 PM, when I paste the link into another sheet, a bunch of zero's appear. How do I get it to link without having the blank cells turn into zero's, can it be kept blank at all? I changed the formatting of the WS I am linking to General, just to see what happens, but no luck, still zero's.

Hope you understand what it is I am trying to explain.

Thanking you for your help in advance!

[daddylonglegs]

Try this formula for D4

=IF(COUNT(B4,C4)=2,MOD(C4-B4,1)*24,"")

Then for the week

=SUM(D4,G4,J4,M4,P4,S4,V4)-SUM(D5,G5,J5,M5,P5,S5,V5)

If you want to reference a cell on another sheet but return blank if that cell is blank you can use a formula like

=IF(sheet1!A1="","",sheet1!A1)

[Richard Buttrey]

Hi,
You could also hide column D (if necessary) and put in E4

=IF(ISERROR(D4),0,D4)

So that entering 'Off', "holiday etc in B4 or C4 won't affect the calculation
HTH