**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

Hi

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

ok

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

Excelchat Expert
07/05/2018 - 11:36

See now

Excelchat Expert
07/05/2018 - 11:36

changed the close time to 8 then

Excelchat Expert
07/05/2018 - 11:36

Hope this helps

User
07/05/2018 - 11:36

great thank you so much

Excelchat Expert
07/05/2018 - 11:36

Welcome

Excelchat Expert
07/05/2018 - 11:36

Please do give good rating

User
07/05/2018 - 11:36

will do!

Excelchat Expert
07/05/2018 - 11:36

It would really helps

Excelchat Expert
07/05/2018 - 11:37

Thanks

Excelchat Expert
07/05/2018 - 11:37

Have a great day

Excelchat Expert
07/05/2018 - 11:38

You can provide your rating once you end the session

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*