Calculate Date and Times (based on Business days)
 

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,

Calculate Date and Times (based on Business days)
 

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





"Diane" wrote:

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,

Calculate Date and Times (based on Business days)
 

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


"Diane" wrote in message
...
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,

Calculate Date and Times (based on Business days)
 

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,


"Francis" wrote:

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





"Diane" wrote:

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,

Calculate Date and Times (based on Business days)
 

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,

"Fred Smith" wrote:

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


"Diane" wrote in message
...
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,

.

Calculate Date and Times (based on Business days)
 

What happened when you tried it?

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

Regards,
Fred

"Diane" wrote in message
...
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,

"Fred Smith" wrote:

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


"Diane" wrote in message
...
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,

.

Calculate Date and Times (based on Business days)
 

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,

"Fred Smith" wrote:

What happened when you tried it?

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

Regards,
Fred

"Diane" wrote in message
...
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,

"Fred Smith" wrote:

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


"Diane" wrote in message
...
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,

.


.

Calculate Date and Times (based on Business days)
 

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



"Diane" wrote in message
...
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,

"Fred Smith" wrote:

What happened when you tried it?

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

Regards,
Fred

"Diane" wrote in message
...
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,

"Fred Smith" wrote:

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


"Diane" wrote in message
...
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,

.


.

Calculate Date and Times (based on Business days)
 

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

"Fred Smith" wrote:

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



"Diane" wrote in message
...
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,

"Fred Smith" wrote:

What happened when you tried it?

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

Regards,
Fred

"Diane" wrote in message
...
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,

"Fred Smith" wrote:

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


"Diane" wrote in message
...
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,

.


.


.

Calculate Date and Times (based on Business days)
 

Glad to help. Thanks for the feedback.

Regards,
Fred

"Diane" wrote in message
...
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

"Fred Smith" wrote:

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



"Diane" wrote in message
...
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,

"Fred Smith" wrote:

What happened when you tried it?

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

Regards,
Fred

"Diane" wrote in message
...
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,

"Fred Smith" wrote:

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


"Diane" wrote in message
...
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,

.


.


.