Setting work time at Excel

T

Thiyagu

Board Regular
Joined
Nov 21, 2009
Messages
131
Hi ,

Anyone can help me on below problem's,,.,I did attached examples below..

<table x:str="" style="border-collapse: collapse; width: 479pt;" border="0" cellpadding="0" cellspacing="0" width="638"><col style="width: 99pt;" width="132"> <col style="width: 59pt;" width="78"> <col style="width: 96pt;" width="128"> <col style="width: 114pt;" width="152"> <col style="width: 111pt;" width="148"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 99pt;" height="18" width="132">My working time</td> <td style="width: 59pt;" width="78">7am to 7pm</td> <td style="width: 96pt;" width="128">12 hrs</td> <td style="width: 114pt;" width="152">
</td> <td style="width: 111pt;" width="148">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 40.5pt;" height="54"> <td class="xl28" style="height: 40.5pt; width: 99pt;" height="54" width="132">Date Start</td> <td class="xl29" style="width: 59pt;" width="78">Time Work</td> <td class="xl29" style="width: 96pt;" width="128">Days work (Time work Divide 24)</td> <td class="xl29" style="width: 114pt;" width="152">Date stop (Date Start + Days Work)</td> <td class="xl29" style="width: 111pt;" width="148">Result</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl26" x:num="">12</td> <td class="xl27" x:num="0.5" x:fmla="=B4/24">0.50 </td> <td class="xl25" x:num="40408.791666666664" x:fmla="=A4+C4">8/18/10 7:00 PM</td> <td>O.K</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl26" x:num="">13</td> <td class="xl27" x:num="0.54166666666666663" x:fmla="=B5/24">0.54 </td> <td class="xl25" x:num="40408.833333333328" x:fmla="=A5+C5">8/18/10 8:00 PM</td> <td>Getting wrong Result</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl25" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl26" x:num="">13</td> <td class="xl27" x:num="0.54166666666666663" x:fmla="=B6/24">0.54 </td> <td class="xl25" x:num="40409.333333333336">8/19/10 8:00 AM</td> <td>Expecting Result</td> </tr> </tbody></table>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Say you have the normal Starting Time (7:00 AM) in D5 and the normal Ending Time (7:00 PM) in E5. Have your starting DateStamp (=date + time) in D9, working hours in E9 and the "Days Work" -formula (=E9/12) in F9.

Then the formula to return the desired Date Stop would be:

=IF((D9+F9)>(D9-D5+E5),(D9+1)+(F9-(E5-D5)),D9+F9)

ie IF((starting time + working hours)>(Starting Date at 7 PM),(the next day + (working hours - 12), starting time + working hours)

The formula is not perfect but works with your examples.
 
Upvote 0
Really appreciate for ur help...You manage to help me on what i want ..
This formula work great but for more than 25 hrs it's getting wrong result..Attached the example..for my application
 
Upvote 0
sorry ,

Really appreciate for ur help...You manage to help me on what i want ..
This formula work great but for more than 25 hrs it's getting wrong result..Attached the example..for my application the "days work" it's not fixed it's can go up to 100 hrs , this value is sum from other cell.
Attached the example

<table x:str="" style="border-collapse: collapse; width: 474pt;" border="0" cellpadding="0" cellspacing="0" width="630"><col style="width: 100pt;" width="133"> <col style="width: 82pt;" width="109"> <col style="width: 64pt;" width="85"> <col style="width: 100pt;" width="133"> <col style="width: 128pt;" width="170"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt; width: 100pt;" height="18" width="133">DateStamp</td> <td class="xl25" style="width: 82pt;" width="109">working hours</td> <td class="xl25" style="width: 64pt;" width="85">day work</td> <td class="xl24" style="width: 100pt;" width="133">Date Stop</td> <td class="xl25" style="width: 128pt;" width="170">Result</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">13</td> <td class="xl27" x:num="0.54166666666666663" x:fmla="=B2/24">0.54 </td> <td class="xl26" x:num="40409.333333333328">8/19/10 8:00 AM</td> <td class="xl25">Getting Result--Correct</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">24</td> <td class="xl27" x:num="1" x:fmla="=B3/24">1.00 </td> <td class="xl26" x:num="40409.791666666664">8/19/10 7:00 PM</td> <td class="xl25">Getting Result--Correct</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">25</td> <td class="xl27" x:num="1.0416666666666667" x:fmla="=B4/24">1.04 </td> <td class="xl26" x:num="40409.833333333328">8/19/10 8:00 PM</td> <td class="xl25">Getting Result--Wrong</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt;" x:num="40408.291666666664" height="18">8/18/10 7:00 AM</td> <td class="xl25" x:num="">25</td> <td class="xl27" x:num="1.0416666666666667" x:fmla="=B5/24">1.04 </td> <td class="xl26" x:num="40410.333333333336">8/20/10 8:00 AM</td> <td class="xl25">Expecting result</td> </tr> </tbody></table>
 
Upvote 0
With the same data layout try:

=IF((D9+F9)>(D9-D5+E5),(D9+ROUNDDOWN(E9/(E5-D5)/24;0)+(($E$9/24)-ROUNDDOWN(E9/24;0))),D9+F9)

Basically the same function as before but the +1 day part has been replaced with "total working hours / hours in a normal workday".

It's still not perfect (= what if the person showed up earlier than expected?) but should work with the given data.
 
Upvote 0
Are you excluding any weekdays from your calculations ? (eg Sat-Sun)

Below is a variation on WORKDAY based calculation which uses only working hours:


Excel Workbook
ABCD
1DateStampworking hoursday workDate Stop
218/08/2010 07:001319/08/2010 08:00
318/08/2010 07:002419/08/2010 19:00
418/08/2010 07:002520/08/2010 08:00
518/08/2010 07:002520/08/2010 08:00
Sheet3


It does assume however that start date time is within a valid working window.

Prior to XL2007 the use of WORKDAY function requires activation of the Analysis ToolPak Add-In (Tools -> Addins)
 
Upvote 0
Really thanks a lot for both of u...both formula work for me but the most suitable is the below one..



=WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-"07:00")/("19:00"-"07:00"),1)-1)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-"07:00","19:00"-"07:00")+"19:00"-"07:00"

Again thanks a lot...

May god bless u all.
 
Upvote 0
Hi DonkeyOte,

Can u pls provide me again the formula but don't excluded saturday & Sunday...I have another formula to exclude this cause not all the saturday is my offday..I attached again the formula just do some correction to included saturday & Sunday in formula..


WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-"07:00")/("19:00"-"07:00"),1)-1)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-"07:00","19:00"-"07:00")+"19:00"-"07:00"
 
Upvote 0
Sorry , maybe it's not so clear for u..Pls don't include saturday & sunday inside the formula calculation , i have another formula to exclude this....

Just provide me formula with included saturday & sunday inside calculation , later i use another formula to exclude this since not all my saturday is offday.

=WORKDAY(A2,CEILING((B2/24+MOD(A2,1)-"07:00")/("19:00"-"07:00"),1)-1)+MOD(A2,1)+B2/24-CEILING(MOD(A2,1)+B2/24-"07:00","19:00"-"07:00")+"19:00"-"07:00"
 
Upvote 0
I have seen your other thread on this topic and to be honest I'm not sure you've quite thought this through in full - but to answer your question:


Excel Workbook
ABCDEFGH
1DateStampworking hoursday workWEEKDAYANY DAYStartEnd
218/08/2010 07:001319/08/2010 08:0019/08/2010 08:0007:0019:00
318/08/2010 07:002419/08/2010 19:0019/08/2010 19:00
418/08/2010 07:002520/08/2010 08:0020/08/2010 08:00
519/08/2010 07:003724/08/2010 08:0022/08/2010 08:00
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
299
awoohaw
awoohaw
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
A
  • Question
Export multiple Ranges/Charts to PowerPoint
Replies
1
Views
740
abc_xyz
A
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
475
fjns
F
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13

Forum statistics

Threads
1,214,520
Messages
6,120,017
Members
448,937
Latest member
BeerMan23

Share this page

We've detected that you are using an adblocker.

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.
Go back
Back
Top