IF dates match, and times are consecutive, add a value.

[jm1g]

So iv been working on a spreadsheet, and its really the first time iv played with excell formulas beyond simple If commands, but im doing OK and enjoying it!
The spreadsheet Im working on is for logging my work shifts. What I want help with is a formula to check the times and dates of the shifts entered, and add a value to a cell if the criteria is met.

So, the company is incentive based and each shift can attract certian bonuses. One of the bonuses is working consecutive shifts. I have been using check boxes up to now for this task, but would like to use a formula.

Each shift gets entered into the spreadsheet, with the date and time. There can be between 1 and 4 shifts per day (e.g 1pm, 2pm, 3pm, 4pm).
So lets say I work 1pm , 3pm and 4pm slots. The total money earned per shift for 1pm would not attract the consecutive shift bonus, so I would want that to add nothing to the total. However, 3pm and 4pm are consecutive shifts, so would attract the extra money, which is where I would want the money to be added to the total earned/shift cell. if 1pm, 2pm, 3pm, and 4pm are all worked in a given day (must be same day/date), then each shift would attract the bonus.

I hope iv explained myself clearly! I have some more questions to come, but this is the current obstacle im facing regarding this spreadsheet.

Thanks in advance!

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[jm1g]

Attached. hope it has worked!

[AliGW]

Well done!

Unfortunately, one critical bit of information is missing. Please tell us what your expected outcomes are for the data you've provided.

[jm1g]

Expected outcome is that cell N2 (the bonus amount) is added to the respective H colomn cell IF the shift times are consecutive. If not, do nothing. As you can see from the spredsheed, that current task is done by checkboxs in F colomn, however I would prefer it if the F colomn was TRUE FALSE for consecutive shifts or not, adding the bonus to the $/shift if true. I hope im making sense!

[Glenn Kennedy]

This formula will give you the correct bonus addition:

=IF(OR(B1=B2-1/24,B3=B2+1/24),$N$2,0)

I'm not sure how the remainder of the formula fits round that, though....


=IF(OR(B1=B2-1/24,B3=B2+1/24),$N$2,0)

[jm1g]

Im pretty new to excel wizardry so this doesnt make much sense to me

[Glenn Kennedy]

maybe this:

=IF(C2=FALSE,0,((SUMIF(C2:C2,TRUE,$K$2))+(SUMIF(D2,TRUE,$L$2))+(SUMIF(E2,TRUE,$M$2))+IF(OR(B1=B2-1/24,B3=B2+1/24),$N$2,0)+(SUMIF(G2,TRUE,$O$2))))

But since you did not tell us what your manually-calculatd answers are - I cannot be sure!!!

[jm1g]

Im not sure what you mean. Everything you should need is included in the spreadsheet. After some playing around, your formula seems good, and I can edit it to include an If command to check the dates are the same. If anyone else has any ideas though, Im all ears!

[Glenn Kennedy]

If your formula is incorrect in any way: the answers it generates will be wrong. That's why we ask for manually calculated results. It enables us to match your words and your expected answers with what's on the sheet.. I rarely look at the "wrong" formula. What's the point? if it's wrong, it's wrong.

Anyway.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[jm1g]

Ah ok. There wasn't any wrong formula, just a formula that needed an extra part adding to detect the consecutive dates. If true, and the times are consecutive, I want it to add a value to the cell. In the spreadsheet you will see a column for $/shift, which has a string of sumif's that are linked to checkbox cells. The consecutive time formula Iv requested would be added onto that long formula somehow. Somebody posted above with an OR formula that I'm going to play with. If I still can't crack it, il be back.

P.s any good YouTube channels for learning the basics of formulas? There's so many to choose from, which ones stand out?

[FlameRetired]

I am stuck, too.
Why is E6 not TRUE? It is same date and is a consecutive shift.

[FlameRetired]

So, the company is incentive based and each shift can attract certian bonuses. One of the bonuses is working consecutive shifts. I have been using check boxes up to now for this task, but would like to use a formula.

It appears you need to retain at least the 'Shift Complete?' check box. Is that correct?

[Glenn Kennedy]

This is (should be) fairly simple. However, you seem very unwilling to provide a clear explanation of what you EXPECT the formula todo.

So, one last attempt: please explain, step-by-step what you want a formula to do...

Some feedback on whether the formula at Post 8 worked aor didn't work (and if it didn't work - what answer did you expect to get.

If you don't help us, we can't help you. Simple as that.

[jm1g]

So I want the formula to check if the shift was worked consecutively to another one. If 3 shifts are worked on the same day, in consecutive timeslots, then it will attract an extra bonus to the $/shift colomn, for each shift..

So in the case of the spreadsheet I sent, N2 is the bonus amount for consecutive shifts. H colomn works out the $/shift for the respective row.

So if 2 shifts are worked on 28/7/17, but are not consecutive, then the other formulas will work out the pay for the shift, but if it detects they are consecutive, then it will add a bonus amount to the $/shift cells for those shifts.

and to answer somebodys question, Yes, if shift complete is not checked, all other bonuses dont count and the cell is returned as 0

[jm1g]

Post 8 did work, heres how I edited it slightly.
=IF(OR(B5=B6-TIME(1,0,0),B7=B6+TIME(1,0,0)),$N$2,0)
This would be where row 6 would be the row in question, so its checking the one above it, and the one below it for a + or - 1hour change in times. Now i need to incorperate a check to see if the dates are the same before implemeting this formula.

[AliGW]

Now you need to upload your latest version of the Excel file.

[Glenn Kennedy]

Yes please...