Previous Topic Next Topic

IF Function Time

Solved/Closed
Ashakil - 10 Apr 2016 à 08:42
 Ashakil - 11 Apr 2016 à 10:07
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?
Related:

1 response

Réponse 1 / 1
Mazzaropi Posts 1985 Registration date Monday 16 August 2010 Status Contributor Last seen 24 May 2023 147
11 Apr 2016 à 09:47
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
11 Apr 2016 à 10:07
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
Sasha -
ac3mark -

1 response
Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
Looking For a Value in a Cell
Tom9o -
rizvisa1 -

3 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How to specify macro for current active sheet
seo5906 -
Dave -

18 responses