• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Time

T

trickyd

New Member
Hello Guru's,


I have tried and failed numerous times with this problem.


I'm creating a time sheet and need to record any time that is worked between the hours of 16:00 and 19:00. The time can only be entered in 15min increments ie 01:15, 21:45 etc.


So if for instance someone works from 15:30 to 17:45 I need the formula to display the result of 1.75 hours worked in this period, and if it is 15:00 to 20:00 I need it to show the full 3 hours worked between 16:00 and 19:00.


I have tried 3 and 4 nested IF formulas but I'm going wrong somewhere.


Help!!! and Thanks!!!
 
Hi Trickyd,


Welcome to Chandoo.org!!


Need few clarifications in your problem:


1. Who enters the time, users? Manually or to be tracked automatically on real time basis?

2. 15:30 - 17:45 should be 2.25 and not 1.75 hrs, correct?

3. you state "and if it is 15:00 to 20:00 I need it to show the full 3 hours worked between 16:00 and 19:00.", so does it mean either start or end time should be between 16:00 to 19:00 to consider the working hrs?


Appreciate if you can also share details of what your current time sheet looks like.


Regards,

Prasad DN
 
Hi Trickyd,


Will it be possible for you to provide few example data set and the results you want it ?


Cheers,


Rahul
 
Hi all,


The start time is present in A1, End time in A2, try this formula:


=IF(AND((TIMEVALUE(B2)-TIMEVALUE("00:"&MID(B2,FIND(":",B2)+1,3)))=0,(TIMEVALUE(B3)-TIMEVALUE("00:"&MID(B3,FIND(":",B3)+1,3)))=0),(TIMEVALUE(B3)-(TIMEVALUE(B3)-TIMEVALUE("00:"&MID(B3,FIND(":",B3)+1,3)))-(TIMEVALUE(B2)-(TIMEVALUE(B2)-TIMEVALUE("00:"&MID(B2,FIND(":",B2)+1,3)))))*1440/60,(TIMEVALUE(B3)-TIMEVALUE(B2))*1440/60)


..and have a look at this sheet.

http://dl.dropbox.com/u/60644346/Copy%20of%20TIME.xlsx


Faseeh
 
This is for a management ot recording tool. Yes it would be 2.25 hours! I am mentally incapacitated from thinking too hard...


The time is entered by the sheet owner so will be single user.


Start and finish time are in adjacent cells, and the shift time is already calculated.


What I need the formula to do is calculate any of the shift time that falls inbetween the hours of 16:00 and 19:00. And I need it to be presented in decimal format.


I hope this makes sense now?
 
Hi, trickyd!


Let me see if I got it or if understood quite wrong. Assume this is your data from cell A1 in advance:

-----

[pre]
Code: 
From		To		Limit from 	Limit to	Elapsed time
15:30:00	15:45:00	16:00:00	19:00:00	00:00:00
15:30:00	17:45:00	16:00:00	19:00:00	01:45:00
15:30:00	21:00:00	16:00:00	19:00:00	03:00:00
16:30:00	18:45:00	16:00:00	19:00:00	02:15:00
16:30:00	21:00:00	16:00:00	19:00:00	02:30:00
20:30:00	22:00:00	16:00:00	19:00:00	00:00:00
[/pre]
-----


First, check if output data in column E is correct.

If not, I apologize and wait for your feedback.

If yes, then try this:


a) set data validation (Data tab, Data Tools group, Data Validation icon) for columns A:B (or A:D) to Configuration, Validation Criteria, Allow/Let/Permit Custom, Formula:

=A2*4*24-ENTERO(A2*4*24)=0 -----> in english: =A2*4*24-INT(A2*4*24)=0


b) in E2 type and copy down:

=SI(MIN(B2;D2)>=MAX(A2;C2);MIN(B2;D2)-MAX(A2;C2);0) -----> in english: =IF(MIN(B2,D2)>=MAX(A2,C2),MIN(B2,D2)-MAX(A2,C2),0)


Just advise how it goes on.


Regards!


@prasaddn

Hi!

I think that the value of 1.75 for the difference between 15:30 to 17:45 was right and not the one of 2.25. It's because of the 16:00-19:00 window constraint. Isn't it?

Regards!


@Faseeh

Hi!

Being paid by characters in formula? Should be millonaire... I remain as poor as a fifth world country inhabitant.

:)

Regards!
 
@ Hi SirJB7, heheheh Yup, its a long formula.. :D (don't agree with your second last statement btw)


Regards!
 
Try this for the final formula


you have the lower limit in B2,

The Upper limit in B3

Start Time in Column A, End Time in Column B, Difference in Column C


The formula in Column C is =(B4-A4)-(($B$1-A4)*(A4<$B$1))-((B4-$B$2)*(B4>$B$2))

(Note extra brackets were used for clarity)

`Lower Limt 16:00

Upper Limit 19:00

Start Time End Time Difference

17:30 18:00 0:30

15:00 18:00 2:00

15:00 20:00 3:00

15:00 17:30 1:30

13:00 20:00 3:00
 
@SirJB7


Hi, me!

So long... I wonder where might be trickyd and what would he say... At last customer is always right... Ok, not always but we can't say nothing "a contrario sensu".


Regards!
 
@ kchiba


This works perfectly. Thank you. Mathematics wins everytime!


And I'm on UK time, so this was a perfect start to the day.


Kind regards to all who helped.
 
You must log in or register to reply here.
Back
Top