# If statemenet to determine hours worked per week

• Re: If statemenet to determine hours worked per week

Quote from NBVC;709012

So they can enter a date in column A? Just not in the format of 4/7? If they can enter a date (no matter the format), the calculations in my formula should still work.

I am a little confused about the "real" problem.

They can enter a date, but it has to be entered at "7 APR 2014" and other method excel rejects. It seems that Excel does not recognize it as a date and does not allow the formuals to work to calculate the hours worked per week.

• Re: If statemenet to determine hours worked per week

But why can't they just enter it as you showed. Why do they have to enter it as 4/7?

The only way to convert is to use a formula, but you can't put the formula in the data validation area.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: If statemenet to determine hours worked per week

Will this work for all Months?

• Re: If statemenet to determine hours worked per week

It should. If the month has only 4 weeks, the Week 5 cell will have a 0 result.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: If statemenet to determine hours worked per week

How would the formula change if we wanted the weeks to be calculated from Monday to Sunday of each week?

Thank you

• Re: If statemenet to determine hours worked per week

Try:

[COLOR="#0000FF"]=SUMIFS(\$D\$4:\$D\$30,\$A\$4:\$A\$30,">="&\$I\$1-WEEKDAY(\$I\$1,3)+7*(MOD(ROWS(\$A\$1:\$A1),7)-1),\$A\$4:\$A\$30,"<"&\$I\$1-WEEKDAY(\$I\$1,3)+7*(MOD(ROWS(\$A\$1:\$A2),7)-1))[/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: If statemenet to determine hours worked per week

Do you know if there is a way to make excel drop downs in a document able to scroll with the mouse wheel? If so let me know what code needs to be included.

• Re: If statemenet to determine hours worked per week

Click the cell with the drop down menu, then point to the up or down arrow at the right of the list and scroll with your mouse wheel.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: If statemenet to determine hours worked per week

Any way to use it like a regular dropdown where you do not need to hover on the bar to the right?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!