Asking for help to set up a time sheet using If(OR and MOD

**JillieQ** · 01-26-2017, 08:20 AM

I could use some help please.

I am setting up a time sheet with four possible hour entries:
Start/Lunch Out/Lunch In/Stop And daily totals
I want to Total the daily hours using Clock values (60 minutes) – not using results in Base 100 (i.e. I want results to be 15 and 30 – NOT 25 and 50).

We all know that Excel makes this difficult by having the TIME formats include the Date - so that 8:00(AM) becomes 1/8/1900 12:00:00AM u0on entry into a Time formatted cell.

So I did a work around – and I need some help “tweeking” it. Please.

For this request here are some sample data:
Column C D E F G H
Row 9 8:00 12:00 12:30 16:30 TOT-in Base 100 =INT(G9)

Row 27 8:00 12:20 12:50 17:00 TOT-in Base 100 =INT(G27)

I
Row 9 =MROUND(MOD(G9,1)*60,15)/100

Row 27 =MROUND(MOD(G27,1)*60,15)/100

I am using an IF(OR formula to test the data in the cell containing the results of the MOD formula (I).

An abbreviated copy (Non-Nested) of my Nested IF(OR formula is in column L. ( i.e.):
L
Row 9 =IF(OR(I9=0.00,I9=0.60),0.00,"error")
Row 27 =(IF(OR(I27=0.70,I27=0.30),0.30,”error”)

What I am encountering is this:
1) Excel keeps changing the decimals within my formula – i.e. 0.00
becomes 0 and 0.30 become 0.3. And I wonder if this might be
preventing my getting good results – because -
2) All but the first test IF(I9=0) [I]- after Excel changes the decimal - in
my nested IF(OR formula, returns FALSE – (and so “error”). And all
nested If(ORs are coded identically and the formatting of the cells to
be tested is also identical.

My questions:
1) Does Excel permit the testing of the results of one formula (MOD) by
another formula (IF(OR)?
2) How do I get Excel to stop changing my test data within the formula
(or does it matter)?
3) Is there a special way to format the cells (both the Data cells and the
Formula cells) so that the IF(OR formula can “see” it for testing
purposes?
I’ve tried several of the formats so far - with no affect.

Any help would be greatly appreciated.

Frustrated.

**Glenn Kennedy** · 01-26-2017, 09:45 PM

This might be easier if we had a worksheet to play with.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Glenn Kennedy** · 01-26-2017, 09:55 PM

On re-reading this, I'm maybe a bit clearer. If you enter 08:30 as EXACTLY that 0 8 : 3 0, Excel will automatically format that as TIME. So, a simple subtravction is all you need.

=(D9-C9)+(F9-E9)