Google Sheets: If/Then statement

[CobaltMaven]

Hello,

I am once again asking for your assistance. Thank you for all the help you have given me in the past.

I am trying to add a formula to the line below that automatically checks to see if the employee has worked 8 hours+ and if so, subtracts 30 min from the time. here is what i have so far:

=Iferror(((D4-C4)),-TIME(0,30,0)), )

Now i just need it to check the if the time is over 8 and then make the minus 30 min conditional.

(this is purely for planning purposes only, our employees get more than 30 min for lunch, and it is paid. I promise our employees are happy, well rested and paid fairly.)

[FDibbins]

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed 01 Sep 2021) is actually 44440

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So you really just need to test for > 0.333 or 8/24, then subtract 0.020833333 (or 30/60/24 )

[CobaltMaven]

I didn't know that, thank you!

If/Then statements are a weakness of mine, how would your advise plug into the example above?

Please and Thank you!

[CobaltMaven]

Also, along a similar line, how would i be able to detect how many hours took place before 8:30am or after 5:00pm?

[janmorris]

are you able to upload a spreadsheet (or provide a link to google file) with some sample data, and also some examples of expected/anticipated results, and some notes that explain what other columns/cells might be needed in order to calculate the results you expect to see?

[FDibbins]

Depending on what you want to do, maybe something like...

=if(calc/cell containing shift >8/24, cell-30/60/24,cell)