Well, the explanation would have a logical and a technical part. Without context, we can only guess the logical part.
So that leaves the technical part.
Shortly summarized it is a conditional calculation of some values. The result is rouded to 2 decimals.
It looks like the formula is designed to be copied down.
Cells $W$8 and $W$5 are TRUE/FALSE values.
If $W$8 is TRUE then the calculation is: SUM(H$12:H12)+G13-$W$9. If the outcome is negative, then 0 is returned. Also if $W$8 is FALSE.
Remark: on the first line with this formula, SUM(H$12:H12)+G13+$W$9 is the value in H12+G13-$W$9.
Copied down 1 cell gives the sum of H12 and H13 + G14 - $W$9.
Copied down another cell gives the sum of H12,H13 and H14 + G15 - $W$9. Etcetera.
If $W$5 is TRUE, then $W$6 is subtracted from G13, with a maximum of 0.
Copied down 1 cell gives G14-$W$6, with a maximum of 0.
From the results of both conditions ($W$8 / $W$5), the highest value is returned and rounded to 2 decimals.
So with both conditions the value in G13 (copied downwards G14, G15 etcetera) is used in the calculation.
With condition $W$8 this is added to the cumulative value in H12 and downwards. And $W$9 is subtracted from this.
With condition $W$5 only $W$6 is subtracted from G13 (and downwards G14, G15 etcetera).
In no case negative values will result. The result will Always be 0 or positive,
I guess the value in $W$6 is associated with the condition in $W$5 and $W$9 is associated with the condition in $W$8.
Well, the word thank you is not enough for you but I have nothing else to say
Let's go back to the formula
What this formula do is it gets two time stamps in 24 hours format and calculates the hourse between them like if we want to calculate the gap between check-in and check-out for a visitor
But it seems super-complicated
Is there any replacement for it that would be simple, easy to understand and do the same job?
If A1 = time in; B1 = time out, then the formula would be just
Code:
=B1-A1
If possibly midnight is within the time frame, use
Code:
=MOD(B1-A1,1)
If time gap would be >= 24 hours, you would need to include the date, use the first formula and make sure your result field is formatted as [h]:mm or [h]:mm:ss.
To be honest I really don't recognize this kind of calculation from the original formula, e.g. it doens't make sense to round a time gap on 2 decimals: .01 = 14 minutes and 24 seconds.
Do you know the contents of the fields used in the original formula?
Yeah
actually the person who wrote the formula wants to count the hours only not the hours and minutes
so he want to say that if someone checked in at 17:00 and checked out at 19:30, this person stayed for 2.50 Hours not 2 Hours and 30 Minutes
you get that ?
well the problem is: I don't have the fields G,H and W, the thread where I copied the formula from is deleted and that's why I needed an explanation
I just need to calculate hours between two fields that contains date and time in this format: (dd/mm/yyyy hh:mm)
I want two things to be considered:
1- the result should be in hours only not in hh:mm format,, for example: 5:15 = 5.25 hours
2- if the date changes it should calculate it for example: check-In: 15/5/2015 23:15 - check-Out: 16/5/2015 01:00 = 1.75 hours
sorry Mr.MarcelBeug for giving the bad feeling ><"
For a time difference you can just subtract the smaller value from the larger one.....then multiply by 24 to get the hours as a decimal (as Marcel says), e.g. with start time/date in A2 and end time/date in B2 use this formula in C2
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.