A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can this even be done? Tracking Date / Time Elapsed



 
 
Thread Tools Display Modes
  #1  
Old January 18th, 2006, 05:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed

I'm trying to create a worksheet which records elapsed time between Date /
Time Received and Date / Time Delivered. So far, so good. Problem is, the
elapsed time I get really needs to use some form of Networkdays, excluding
Holidays, and (here's the really tricky part) non-work hours. I've tried
fiddling with various Ifs, but am not experienced enough at Excel to grasp
why my formulas don't work.

What I currently have is giving me total time elapsed, which is good:

Received (A1) - 12/30/05 16:08
Delivered (B1) - 1/3/06 9:20
Elapsed (C1) - =(A1-B1) 3:17:12 (formatted Custom - d:h:mm)

The result I actually want in this example is 0:2:12, based on 52 elapsed
minutes on 12/30/05 and 1:20 on 1/3/06 (clock stops between 5PM & 8AM),
excluding weekends, holiday (1/2/03) & overnight hours (17:00-08:00). Is
there any *possible* way to get there from here?

Any help greatly appreciated, but as I said, I'm not all that Excel savvy,
so type... real... slow...
Ads
  #2  
Old January 18th, 2006, 05:24 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed

First post, first goof already. My C1 formula actually is =(B1-A1)


  #3  
Old January 19th, 2006, 11:15 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed

Hi

Set up a list of holiday dates somewhere on your sheet and use
InsertNameDefine Holidays Refers to your range of cells.

Then
=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*9

Starting from the end of the formula, Networkdays returns the total
elapsed working days between the start and end dates, excluding any days
within the range of dates defined by holidays. Since we are dealing with
the start date and end date in the earlier part of the formula, then you
need to deduct 2 from the result, and multiply the result by 9 to cover
the hours for each full day elapsed.

The first part of the formula, uses MOD() to extract the time from the
day date value in the cell, and deducts this from 5 pm to calculate
elapsed hours on start day. The middle part does the same, but takes 8
am away from the time value to calculate elapsed hours on the final day.

--
Regards

Roger Govier


"scd" wrote in message
news
I'm trying to create a worksheet which records elapsed time between
Date /
Time Received and Date / Time Delivered. So far, so good. Problem
is, the
elapsed time I get really needs to use some form of Networkdays,
excluding
Holidays, and (here's the really tricky part) non-work hours. I've
tried
fiddling with various Ifs, but am not experienced enough at Excel to
grasp
why my formulas don't work.

What I currently have is giving me total time elapsed, which is good:

Received (A1) - 12/30/05 16:08
Delivered (B1) - 1/3/06 9:20
Elapsed (C1) - =(A1-B1) 3:17:12 (formatted Custom - d:h:mm)

The result I actually want in this example is 0:2:12, based on 52
elapsed
minutes on 12/30/05 and 1:20 on 1/3/06 (clock stops between 5PM &
8AM),
excluding weekends, holiday (1/2/03) & overnight hours (17:00-08:00).
Is
there any *possible* way to get there from here?

Any help greatly appreciated, but as I said, I'm not all that Excel
savvy,
so type... real... slow...


  #4  
Old January 19th, 2006, 05:17 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed

Oh... my... God, what a beautiful thing!
I followed your instructions, c/p'ed the formula (i especially liked that
part!) and it worked perfectly.

Thank you SO much, Roger!


"Roger Govier" wrote:

Hi

Set up a list of holiday dates somewhere on your sheet and use
InsertNameDefine Holidays Refers to your range of cells.

Then
=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*9

Starting from the end of the formula, Networkdays returns the total
elapsed working days between the start and end dates, excluding any days
within the range of dates defined by holidays. Since we are dealing with
the start date and end date in the earlier part of the formula, then you
need to deduct 2 from the result, and multiply the result by 9 to cover
the hours for each full day elapsed.

The first part of the formula, uses MOD() to extract the time from the
day date value in the cell, and deducts this from 5 pm to calculate
elapsed hours on start day. The middle part does the same, but takes 8
am away from the time value to calculate elapsed hours on the final day.

--
Regards

Roger Govier



  #5  
Old January 22nd, 2006, 12:31 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed


Hi Roger & scd

Roger's suggested formula will only work correctly when the delivery
date is the next working day after the received date as in the example
quoted - the 9at the end needs to be 9 hours, i.e.

=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)

however you can simplify this to

=MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"

or to return a blank if either A1 or B1 are blank

=IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")

Finally, is it possible that the delivery date could be outside work
hours, e.g. on a Saturday, what result would be required then?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=502538

  #6  
Old January 22nd, 2006, 02:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed

Hi daddylonglegs

Nice catch of my error in not using * Time(9,0,0) at the end (or even
more simply * "09:00")!
Your simplification to just using the subtraction of the MOD of the
times, and using Netwokdays -1 rather than 2 is a masterly stroke.
Well done.

--
Regards

Roger Govier


"daddylonglegs"
wrote in
message
news:[email protected] um-nospam.com...

Hi Roger & scd

Roger's suggested formula will only work correctly when the delivery
date is the next working day after the received date as in the example
quoted - the 9at the end needs to be 9 hours, i.e.

=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)

however you can simplify this to

=MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"

or to return a blank if either A1 or B1 are blank

=IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")

Finally, is it possible that the delivery date could be outside work
hours, e.g. on a Saturday, what result would be required then?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=502538



  #7  
Old February 3rd, 2006, 07:44 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default Can this even be done? Tracking Date / Time Elapsed

Thanks, both! I realized the same-day problem a few days ago and had
intended to check back in for clarification. Looks like you beat me to it.

Your "if" statement was an even bigger help, as much of our documentation
predates the use of time stamps, and just relies on dates.

And as for your question about what happens during off-times, we basically
told Mgmt that anything we don't get between 8-5 M-F is just stamped as of
8AM the next workday.

Again, thanks to both of you!

"daddylonglegs" wrote:


Hi Roger & scd

Roger's suggested formula will only work correctly when the delivery
date is the next working day after the received date as in the example
quoted - the 9at the end needs to be 9 hours, i.e.

=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)

however you can simplify this to

=MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"

or to return a blank if either A1 or B1 are blank

=IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")

Finally, is it possible that the delivery date could be outside work
hours, e.g. on a Saturday, what result would be required then?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=502538


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Cat Ain't Working shep Setting Up & Running Reports 15 September 12th, 2005 05:14 PM
Do when a time and date has elapsed Michael General Discussion 3 April 24th, 2005 10:46 PM
Calendar Question Josh General Discussion 7 March 28th, 2005 11:19 PM
Seemingly (and probably) complex turnaround time rgrantz Running & Setting Up Queries 1 March 21st, 2005 09:19 AM
Aggregating Date Data into Weeks and Quarters Roger Running & Setting Up Queries 3 July 11th, 2004 05:56 PM


All times are GMT +1. The time now is 12:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.