Calculate Date and Times (based on Business days)

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I have two date & time fields that I need to calcualte the number of days
line is open. I have my first data point in B2 and the one to subtract is in
Q2.

Thanks,
 
Diane

apology but I don't understand your requirement.....
--
Hope this help

Please click the Yes button below if this post have helped answer your needs

Thank You

cheers, francis
 
You want Networkdays, as in:
=networkdays(b2,q2)
or,
=networkdays(q2,b2)
depending on how your data is stored.

If you want to take holidays into account, create a list of them, and feed
them into the function, as in:
=networkdays(q2,b2,a1:a10)

Regards,
Fred
 
Francis,
I am trying to calculate the difference between two fields (data & time
combine in one cell). I need it to take into consideration only business
days as well.

Hope this makes more sense.

Thanks,
 
Fred,
Thanks for the information, does this take into consideration that my fields
store both the date and time into 1 cell? I thought it was tricker than what
you show below.

Thanks,
 
What happened when you tried it?

In answer to your question, Networkdays ignores the time portion of the
cell.

Regards,
Fred
 
Fred,
It just gives me the total number of days (ie. 1/4/10 9:18 AM verus 1/5/10
12:30 PM) gave me a 2.

Do you have any other ideas on how to get them when both date and time are
in one cell?

Thanks,
 
Certainly. In your original post, you asked for the number of "days", which
I assumed excluded the times.

Will your end time *always* be greater that your start time?

If so, use:
=networkdays(b2,q2)+mod(q2,1)-mod(b2,1)-1
You probably want to format this with something like: dd hh:mm

If you need to support more than 31 days, let me know (and what you want the
output to look like).

If your end time can be less than your start time, tell us how you want it
to wrap (eg, number of hours in the working day). For example, if your
date/times are 1/4/10 12:30 PM to 1/5/10 9:18 AM, what answer do you want?

Regards,
Fred
 
Fred,
I think this formula will work and all I have to do is format the cell for
type of data I'm wanting it to return.

Thanks so much for the help.

Sincerely,
Diane
 
Back
Top