# function:time calculation

1. ## function:time calculation

Hi all,

I looked into many topics but my doubt still exists

I need to calculate time according to working hours and working days (excluding sat and sun)
The problem is we have no start date or end date the process continues.
I have attached a sample sheet.
Coloumn A= names
Coloumn B=allotment time
Coloumn C=estimated time where in only extra hours is entered so that coloumn D shows actual date and time.(after adding C with coloumn B).

But this goes out of working hours some times like in example.

I want to use some formula to get result as coloumn E
i.e if the estimated time crosses 6 PM it takes next day in to account.
start time=9:00 Am and end time 6:00PM
Is this possible????  Register To Reply

2. ## re: function:time calculation

Put this formula in D2:

=IF(MOD(B2,1)+C2>TIMEVALUE("6:00:00 PM"),(C2-(TIMEVALUE("6:00:00 PM")-MOD(B2,1))+(INT(B2)+1+TIMEVALUE("9:00:00 AM"))),B2+C2)

Or, without the time values:

=IF(MOD(B2,1)+C2>0.75,(C2-(0.75-MOD(B2,1))+(INT(B2)+1.375)),B2+C2)  Register To Reply

3. ## re: function:time calculation Originally Posted by JBeaucaire Put this formula in D2:

=IF(MOD(B2,1)+C2>TIMEVALUE("6:00:00 PM"),(C2-(TIMEVALUE("6:00:00 PM")-MOD(B2,1))+(INT(B2)+1+TIMEVALUE("9:00:00 AM"))),B2+C2)

Or, without the time values:

=IF(MOD(B2,1)+C2>0.75,(C2-(0.75-MOD(B2,1))+(INT(B2)+1.375)),B2+C2)
thanks a lot..JBeaucaire

just to know how can we reverse it??? in the sense columnD-B gives C??  Register To Reply

4. ## re: function:time calculation

In C2:

=IF(INT(D2)>INT(B2),(MOD(D2,1)-0.375)+(0.75-MOD(B2,1)),D2-B2)  Register To Reply