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

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 upon 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 (L) to test the contents of the cell containing the results of the MOD formula (I).

Following is an abbreviated copy (Non-Nested) of my Nested IF(OR formula is in column (L):
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”).
(All parts of my nested If(ORs are coded identically and the
formatting of all cells in columns (I) are identical as are the
formatting of all cells in columns (L) ).

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.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

JillieQ,

Set cols C - G formats as custom "[h]:mm"

Following formula in G9 will then give you the total daily hours using Clock values:

=IF(C9="",0,IF(C9<D9,D9-C9,1-C9+D9))+IF(E9="",0,IF(E9<F9,F9-E9,1-E9+F9))

Works as folows:
If there is no Start time in Col C, the pre-break session is Zero. If the employee does not come back after lunch, the post-break session is Zero.
If the employee is on a shift that does not go through midnight, it adds the (clocking off - return from lunch),and adds (break for lunch - start time).
If the employee works a Shift running past midnight, the start time could be later than the lunchbreak, or the time they return from the break could be later than the stop time. Where that happens, formula deducts the start time from twenty four hours, then adds the hours for the second day (e.g. starting 23:00 and going for meal break at 03:00 becomes (24:00-23:00)+3:00 = 04:00 hours)

Hope that solves your problem

Ochimus

JillieQ,

Set cols C - G formats as custom "[h]:mm"

Following formula in G9 will then give you the total daily hours using Clock values:

=IF(C9="",0,IF(C9<D9,D9-C9,1-C9+D9))+IF(E9="",0,IF(E9<F9,F9-E9,1-E9+F9))

Works as folows:
If there is no Start time in Col C, the pre-break session is Zero. If the employee does not come back after lunch, the post-break session is Zero.
If the employee is on a shift that does not go through midnight, it adds the (clocking off - return from lunch),and adds (break for lunch - start time).
If the employee works a Shift running past midnight, the start time could be later than the lunchbreak, or the time they return from the break could be later than the stop time. Where that happens, formula deducts the start time from twenty four hours, then adds the hours for the second day (e.g. starting 23:00 and going for meal break at 03:00 becomes (24:00-23:00)+3:00 = 04:00 hours)

Hope that solves your problem

Ochimus