Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Subtracting Night-time-hours

I have a workbook that captures the hours that someone has spent reviewing
documentation (i.e. The time they received it and the time they returned it),
and then it totals the hours. The problem I am having is that if they happen
to not return the document until a few days later than I get an exorbitant
result when in actuality there is only 9 working hours a day and only five
days a week.

Here's what I'm looking at...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 70 hours 51 minutes ' Derrived
from this code -- =IF(D439<FALSE,D439-C439,"")

I need it to be this...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 25 hours 51 minutes ' by
subtracting 15 hours for each night


Does anyone know how I can mathimatically accomplish this by modifying this
code so that it accounts for the evenings and weekends? --
=IF(D439<FALSE,D439-C439,"")


Thank You Sooo Much.
Rob
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Subtracting Night-time-hours

http://www.cpearson.com/excel/DateTimeWS.htm
Working Days And Hours Between Two Dates And Times

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
I have a workbook that captures the hours that someone has spent reviewing
documentation (i.e. The time they received it and the time they returned
it),
and then it totals the hours. The problem I am having is that if they
happen
to not return the document until a few days later than I get an exorbitant
result when in actuality there is only 9 working hours a day and only five
days a week.

Here's what I'm looking at...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 70 hours 51 minutes ' Derrived
from this code -- =IF(D439<FALSE,D439-C439,"")

I need it to be this...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 25 hours 51 minutes ' by
subtracting 15 hours for each night


Does anyone know how I can mathimatically accomplish this by modifying
this
code so that it accounts for the evenings and weekends? --
=IF(D439<FALSE,D439-C439,"")


Thank You Sooo Much.
Rob



  #3   Report Post  
Posted to microsoft.public.excel.programming
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Subtracting Night-time-hours

Holy Cow... Is there an easier way that's not so confusing?

"Tom Ogilvy" wrote:

http://www.cpearson.com/excel/DateTimeWS.htm
Working Days And Hours Between Two Dates And Times

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
I have a workbook that captures the hours that someone has spent reviewing
documentation (i.e. The time they received it and the time they returned
it),
and then it totals the hours. The problem I am having is that if they
happen
to not return the document until a few days later than I get an exorbitant
result when in actuality there is only 9 working hours a day and only five
days a week.

Here's what I'm looking at...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 70 hours 51 minutes ' Derrived
from this code -- =IF(D439<FALSE,D439-C439,"")

I need it to be this...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 25 hours 51 minutes ' by
subtracting 15 hours for each night


Does anyone know how I can mathimatically accomplish this by modifying
this
code so that it accounts for the evenings and weekends? --
=IF(D439<FALSE,D439-C439,"")


Thank You Sooo Much.
Rob




  #4   Report Post  
Posted to microsoft.public.excel.programming
Rob Rob is offline
external usenet poster
 
Posts: 718
Default Subtracting Night-time-hours

OK That gave no help. I did the whole names thing but It doesn't compute the
specific days that I need it to compute and I can't figure out how to make
the codes on that page work with what I need to do.


Feeling Bummed,
Rob

"Rob" wrote:

Holy Cow... Is there an easier way that's not so confusing?

"Tom Ogilvy" wrote:

http://www.cpearson.com/excel/DateTimeWS.htm
Working Days And Hours Between Two Dates And Times

--
Regards,
Tom Ogilvy

"Rob" wrote in message
...
I have a workbook that captures the hours that someone has spent reviewing
documentation (i.e. The time they received it and the time they returned
it),
and then it totals the hours. The problem I am having is that if they
happen
to not return the document until a few days later than I get an exorbitant
result when in actuality there is only 9 working hours a day and only five
days a week.

Here's what I'm looking at...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 70 hours 51 minutes ' Derrived
from this code -- =IF(D439<FALSE,D439-C439,"")

I need it to be this...

Col C Col D Col E
Row 439 08-29-06 9:10 AM 09-01-06 8:01 AM 25 hours 51 minutes ' by
subtracting 15 hours for each night


Does anyone know how I can mathimatically accomplish this by modifying
this
code so that it accounts for the evenings and weekends? --
=IF(D439<FALSE,D439-C439,"")


Thank You Sooo Much.
Rob




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtracting 2 hours from a time.... Literalgar[_2_] Excel Worksheet Functions 2 October 15th 08 07:02 PM
Subtracting more than 24 hours from a Date/Time KSCGerald Excel Worksheet Functions 6 January 3rd 08 07:44 PM
adding/subtracting # of hours from a time HRobertson Excel Discussion (Misc queries) 5 July 12th 07 04:06 PM
Subtracting hours from time Kerry O''''Brien Excel Discussion (Misc queries) 1 October 4th 06 08:31 PM
Subtracting 2 hours from time Chappy Excel Discussion (Misc queries) 2 June 4th 06 05:25 AM


All times are GMT +1. The time now is 02:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"