I created an Excel Spreadsheet with a Total Time for the Duration of the Shift ie 8:30-17:30.

Then I created this formula

=(MOD([@[Supposed Shift End]]-[@[Supposed Shift Start]],1))*24 

with Format cells -> Number 2 decimal places. This gives me the [Total Supposed Shift Hours]

Giving me Duration of the Shift that needs to be covered.

Now I also created another column for the ACTUAL time the shift covered

=(MOD([@[Actual Time End]]-[@[Actual Time Start]],1))*24

This gives me the [Total Actual Time Hours]

For the Actual Time End and Actual Time Start When the employee DIDN'T show up we entered 0 for both cells with the same cell format Number -> 2 decimal places

The Problem:

This is the formula I wrote to subtract these 2 Columns

=(MOD([@[Total Supposed Shift Hours]]-[@[Total Actual Time Hours]],1))*24

Why when I subtract say the [Total Supposed Shift Hours] 9.00 - the [Total Actual Time Hours] 0.00 = 24.00 ???

9 - 0 = 9 not 24.... sigh

Will the formula be affected if the time goes from previous night 21:00 to 8:00 with the MOD formula?

Sample data (as requested)
Note: Nomenclature differs from description above: Open = Supposed and Covered = Actual

CodePudding user response：

Initial Answer
A minor point first: You don't need to wrap the MOD function in brackets. As the function already produces a result to the *24. Thus the following works just fine:

=MOD([@[Supposed Time End]]-[@[Supposed Time Start]],1)*24

To your question: Your non-working formula reads as if it simply wants the difference (in hours) between Supposed and Actual. If that's so, simply do this:

=[@[Total Supposed Shift Hours]]-[@[Total Actual Shift Hours]]

EDIT: Using the (now posted) table, I've constructed what I think it is you're trying to do
Refer to Sample results image below.

Formula in Colum E: =MOD([@[Open Post End]]-[@[Open Post Start]],1)*24
Formula in Colum H: =MOD([@[Covered Post End]]-[@[Covered Post Start]],1)*24
Formula in Colum J: =[@[Total Hours Missing]]-[@[Total Hours Covered]]

Sample results:

Now: If Column J (i.e. the response to your core question) isn't the result you're after, can you tell me what it is you would expect there (using actual expected values for each row).

Notes:

1. Your table column header Total Hours Missing is somewhat confusing.
   But, I'm reading that a Post Duration (Duration of Shift in your original parlance).
   
2. If I understand what transpired correctly, the "spanner in the works" was @P.b suggestion to remove MOD. Revert your formulas in columns E and H as shown above.