IF Function Time

Solved/Closed

Ashakil - Apr 10, 2016 at 08:42 AM
Ashakil - Apr 11, 2016 at 10:07 AM

Hello,

I am trying to make a rota and I need help with a formula.

A1 B1 C1
07:00:00 14:30:00 =b1-a1

The person starts work at 7am and finished at 14h30. C1 represents how many hours they work (which the result is 7h30 or 07:30:00)
However when someone works over 6 hours, I need 30 minutes taken off automatically for their break which they do not get paid for. If they work only 6 hours or less then I want the result to just show 'b1-a1'. Can you help please?

Which function will you use to enter current date in a worksheet cell?
How to enter @ in laptop - Guide
Transfer data from one excel worksheet to another automatically - Guide
Enter key will send your message - Guide
Enter coordinates in google earth - Guide
Find function on mac - Guide

1 response

Answer 1 / 1

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Apr 11, 2016 at 09:47 AM

Ashakil, Good morning.

Try to use:

=IF(B1-A1>TIME(6,0,0),(B1-A1-TIME(0,30,0)),B1-A1)

Please, tell us if it worked for you.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Ashakil
Apr 11, 2016 at 10:07 AM

It worked!! Thank you. I had to remember to convert the cell with the answer into time format afterwards but it worked very well.

Thank you again

Similar discussions

If cell A1 has text then cell B2 has today's Date

How to specify macro for current active sheet

help me to add particular cells

Similar discussions

Subscribe To Our Newsletter!