Expert Solution - Excel IF Problems

Question description:
This user has given permission to use the problem statement for this blog.

Hello - I am creating an excel spread sheet to track hours for my employees. When my employees close the store, they will enter "CL" in their time sheet. When CL is entered in the cell, i want it to calculate the hours worked +1 hour. For instance, if the employee came in at 3PM and worked until close (which for this example is 9 PM) how do write out the formula to calculate 3-9pm plus and extra hour? 7 total hours. Additionally, if they employee did not work till close, say until 8pm, how do make the cell use the correct forumla?

Solved by V. E. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert 07/05/2018 - 11:26

Welcome to Got it Pro

User 07/05/2018 - 11:26

Hi there

Excelchat Expert 07/05/2018 - 11:26

Can you paste your sample data and explain your requirements

User 07/05/2018 - 11:27

I'm trying to calculate total hours worked for each sales associate

Excelchat Expert 07/05/2018 - 11:27

In which cell is the formula required

User 07/05/2018 - 11:27

D11

User 07/05/2018 - 11:27

But it will apply to all cells in the D column

Excelchat Expert 07/05/2018 - 11:28

in out it is written CL

User 07/05/2018 - 11:28

Yes, CL represents 'close'

Excelchat Expert 07/05/2018 - 11:28

let me write

User 07/05/2018 - 11:29

CL will represent the closing time for sunday which is shown in C6

User 07/05/2018 - 11:30

if the sales associate closes, 1 hour needs to be added to their total time for the day

Excelchat Expert 07/05/2018 - 11:30

instead on 3 AM can we write is as 3?

User 07/05/2018 - 11:30

yes

Excelchat Expert 07/05/2018 - 11:31

apart from sales associate do we need to add 1 hr to others?

User 07/05/2018 - 11:31

No, the function should only apply to hours that use CL

Excelchat Expert 07/05/2018 - 11:32

For all people?

User 07/05/2018 - 11:33

Yes, Managers, Keys, Sales associates

Excelchat Expert 07/05/2018 - 11:34

See now

Excelchat Expert 07/05/2018 - 11:34

=IF(C11="CL",($F$3-B11)*24+1,(C11-B11)*24)

Excelchat Expert 07/05/2018 - 11:34

this is the formula

Excelchat Expert 07/05/2018 - 11:34

Try changing values to see the result

User 07/05/2018 - 11:34

D11 should show 6 hours

Excelchat Expert 07/05/2018 - 11:34

WHy 6?

Excelchat Expert 07/05/2018 - 11:35

It will be 7, one hr has been added

Excelchat Expert 07/05/2018 - 11:35

since it is CL

User 07/05/2018 - 11:35

Close is at 8PM on Monday, so if that person started work at 3PM and worked until 8PM it would be 5 hours, but becase it is CL we add 1 to amke it 6