Hi, I have a workbook that is layed out as follows:

Date Submitted / Date Expired / Date Approved / Duration

Date expired = 10 working days after Date Submitted minus Holidays

I tried this formula

=if (C6,workday(C6,10,holidays),"")

Which works except if the dates are from Dec 08 to Jan 09
I do get a numerical answer (not an error) but it is the wrong answer
When the dates are from (whatever month) 09 to 09 it works perfectly.


Second problem is calculating the duration.
Number of working days (- holidays) from the Date Submitted to the Date
Approved.

I have this formula but again it doesn't work when counting from 08 to 09.
And I haven't figured out how to exclude the holidays

=if(C6="","",networkdays(C6,if(E6="",today(),E6)))-1

I'm really not that good with excel, so I would appreciate any help offered.

Thanks in advance

It sounds like you haven't entered your holidays properly. What's in the
Holidays range? And, what answer do you get, and what do you expect?

Regards,
Fred.

"Judy L" wrote in message
...
Hi, I have a workbook that is layed out as follows:

Date Submitted / Date Expired / Date Approved / Duration

Date expired = 10 working days after Date Submitted minus Holidays

I tried this formula

=if (C6,workday(C6,10,holidays),"")

Which works except if the dates are from Dec 08 to Jan 09
I do get a numerical answer (not an error) but it is the wrong answer
When the dates are from (whatever month) 09 to 09 it works perfectly.


Second problem is calculating the duration.
Number of working days (- holidays) from the Date Submitted to the Date
Approved.

I have this formula but again it doesn't work when counting from 08 to 09.
And I haven't figured out how to exclude the holidays

=if(C6="","",networkdays(C6,if(E6="",today(),E6)))-1

I'm really not that good with excel, so I would appreciate any help
offered.

Thanks in advance

Hi, thanks for the quick response.

I have 12 holiday days in range H7 through H18
The dates are down this way - Day/Month/Year (all dates formatted this way)
They are in order starting Dec 25 2008 all the way to Dec 26 2009.

This is the result I am getting:

Date Submitted Date Expired Date Approved Duration

18/12/08 06/01/09 (blank) 49 (Days)

The correct answer should be 40 days, since the date approved is blank it
would count up to "today" from the submitted date.

Hope this helps, sorry for the poor explanation.

Thanks again

"Fred Smith" wrote:

It sounds like you haven't entered your holidays properly. What's in the
Holidays range? And, what answer do you get, and what do you expect?

Regards,
Fred.

"Judy L" wrote in message
...
Hi, I have a workbook that is layed out as follows:

Date Submitted / Date Expired / Date Approved / Duration

Date expired = 10 working days after Date Submitted minus Holidays

I tried this formula

=if (C6,workday(C6,10,holidays),"")

Which works except if the dates are from Dec 08 to Jan 09
I do get a numerical answer (not an error) but it is the wrong answer
When the dates are from (whatever month) 09 to 09 it works perfectly.


Second problem is calculating the duration.
Number of working days (- holidays) from the Date Submitted to the Date
Approved.

I have this formula but again it doesn't work when counting from 08 to 09.
And I haven't figured out how to exclude the holidays

=if(C6="","",networkdays(C6,if(E6="",today(),E6)))-1

I'm really not that good with excel, so I would appreciate any help
offered.

Thanks in advance

As I don't have your holidays, I couldn't test this to be sure, but I believe
these work
Expired date:
=IF(ISBLANK(C6),"",WORKDAY(C6,10,$H$7:$H$14))
Duration:
=IF(ISBLANK(C6),"",NETWORKDAYS(C6,IF(ISBLANK(E6),T ODAY(),E6),$H$7:$H$18)-1)

I was assuming your submit date was in C6 as a point of reference.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy L" wrote:

Hi, thanks for the quick response.

I have 12 holiday days in range H7 through H18
The dates are down this way - Day/Month/Year (all dates formatted this way)
They are in order starting Dec 25 2008 all the way to Dec 26 2009.

This is the result I am getting:

Date Submitted Date Expired Date Approved Duration

18/12/08 06/01/09 (blank) 49 (Days)

The correct answer should be 40 days, since the date approved is blank it
would count up to "today" from the submitted date.

Hope this helps, sorry for the poor explanation.

Thanks again

"Fred Smith" wrote:

It sounds like you haven't entered your holidays properly. What's in the
Holidays range? And, what answer do you get, and what do you expect?

Regards,
Fred.

"Judy L" wrote in message
...
Hi, I have a workbook that is layed out as follows:

Date Submitted / Date Expired / Date Approved / Duration

Date expired = 10 working days after Date Submitted minus Holidays

I tried this formula

=if (C6,workday(C6,10,holidays),"")

Which works except if the dates are from Dec 08 to Jan 09
I do get a numerical answer (not an error) but it is the wrong answer
When the dates are from (whatever month) 09 to 09 it works perfectly.


Second problem is calculating the duration.
Number of working days (- holidays) from the Date Submitted to the Date
Approved.

I have this formula but again it doesn't work when counting from 08 to 09.
And I haven't figured out how to exclude the holidays

=if(C6="","",networkdays(C6,if(E6="",today(),E6)))-1

I'm really not that good with excel, so I would appreciate any help
offered.

Thanks in advance

Yes, yes, yes!
You're the best
It works great, thank you so much,

Judy L

"Luke M" wrote:

As I don't have your holidays, I couldn't test this to be sure, but I believe
these work
Expired date:
=IF(ISBLANK(C6),"",WORKDAY(C6,10,$H$7:$H$14))
Duration:
=IF(ISBLANK(C6),"",NETWORKDAYS(C6,IF(ISBLANK(E6),T ODAY(),E6),$H$7:$H$18)-1)

I was assuming your submit date was in C6 as a point of reference.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Judy L" wrote:

Hi, thanks for the quick response.

I have 12 holiday days in range H7 through H18
The dates are down this way - Day/Month/Year (all dates formatted this way)
They are in order starting Dec 25 2008 all the way to Dec 26 2009.

This is the result I am getting:

Date Submitted Date Expired Date Approved Duration

18/12/08 06/01/09 (blank) 49 (Days)

The correct answer should be 40 days, since the date approved is blank it
would count up to "today" from the submitted date.

Hope this helps, sorry for the poor explanation.

Thanks again

"Fred Smith" wrote:

It sounds like you haven't entered your holidays properly. What's in the
Holidays range? And, what answer do you get, and what do you expect?

Regards,
Fred.

"Judy L" wrote in message
...
Hi, I have a workbook that is layed out as follows:

Date Submitted / Date Expired / Date Approved / Duration

Date expired = 10 working days after Date Submitted minus Holidays

I tried this formula

=if (C6,workday(C6,10,holidays),"")

Which works except if the dates are from Dec 08 to Jan 09
I do get a numerical answer (not an error) but it is the wrong answer
When the dates are from (whatever month) 09 to 09 it works perfectly.


Second problem is calculating the duration.
Number of working days (- holidays) from the Date Submitted to the Date
Approved.

I have this formula but again it doesn't work when counting from 08 to 09.
And I haven't figured out how to exclude the holidays

=if(C6="","",networkdays(C6,if(E6="",today(),E6)))-1

I'm really not that good with excel, so I would appreciate any help
offered.

Thanks in advance