Forum Discussion
Need Help for Overtime Calculation in duel shift
if anyone understand what i want then please help. thanks
I am still not sure what you require.
I have assumed you want to enter the clock times as a decimal number and I have used DOLLARDE to convert a time expressed as an integer part and a fraction part of an hour in 60ths, such as 1.30, into a time expressed as a decimal part of a day which displays as 1:30 AM or 1:30 PM (that is 1.5 hours).
This should take a time expressed as a decimal number with the point separating hours and minutes and interpret it as a PM time.
= (12 + DOLLARDE(nightStart, 60)) / 24 so 8.30 becomes 8:30 PMReally appreciate your valuable reply, how can i share the excel sheet for better solution. because i'm not able to create the final sheet yet
To calculate PM to AM shift hours you can use this logic =OUT-IN+(IN>OUT). To get total elapsed time, if the date is included, you can use simple subtraction. If you don't include the date, assuming all work is less than 24 hours, you just need a simple comparison =OUT-IN+(IN>OUT). For the multiplier, you'll need to split the minutes into 15 minute segments. So formula will be
=((B2-A2)+(A2>B2))*24Another way could be using MOD() function like
=MOD(B2-A2,1)*24Here is link of stackoverflow question where you may get helpful comment.
I am still not sure what you require.
I have assumed you want to enter the clock times as a decimal number and I have used DOLLARDE to convert a time expressed as an integer part and a fraction part of an hour in 60ths, such as 1.30, into a time expressed as a decimal part of a day which displays as 1:30 AM or 1:30 PM (that is 1.5 hours).
it's working now, thanks for your great support.
