Excel - IF Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.