#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Date formulas

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default Date formulas

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Date formulas

Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the
information it seems as though I need to have a definite start date and end
date. Is there a way around this to keep a continuous worksheet? If I have
to put dates how far out do the dates go? Say a year or longer? And to
enter the "holidays" do I just create a new column and list one date on each
row?

"Kevin B" wrote:

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Date formulas

I think you want =Workday(), too.

I'd create a new sheet--maybe even hide that sheet when I was done. Put all the
holidays in column A. Just keep adding to the bottom of the list--don't leave
any gaps.

Then use a dynamic range name that points at that ever growing list of holidays.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Try ignoring the holidays with a formula like:
=WORKDAY(A1,4)

Then you can your named range of holidays after you type it in.

Remember that =workday() requires the analysis toolpak addin to be checked
(tools|addins).


DRondeau wrote:

Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the
information it seems as though I need to have a definite start date and end
date. Is there a way around this to keep a continuous worksheet? If I have
to put dates how far out do the dates go? Say a year or longer? And to
enter the "holidays" do I just create a new column and list one date on each
row?

"Kevin B" wrote:

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default Date formulas

Go to a blank worksheet and in a column of your choosing enter in the
holidays, 1 per row. The dates below are the holidays of the company Im
working for.
9/4/2006
10/09/2006
11/23/2006
11/24/2006
12/25/2006
12/26/2006

You can extend the number of holidays out as far as you wish and when all
holidays have been entered, click and drag from the first holiday date to the
last holiday date to select them all.
Then click INSERT on the menu, select NAME and then DEFINE and enter the
name Holidays and click OK.
I did not read your prior post close enough and the NETWORKDAYS is for
calculating the number of work days between 2 dates, what you want is the
WORKDAY function.
For example, if A1 has a date and I want to calculate 4 working days from
that date the and display the result in column B, the formula would be:
WORKDAY(A1,4, Holidays)

Or you could do the following:
Col A Col B Col C
9/5/2006 6 WORKDAY(A1,B1, Holidays)

You could also use and IF function so that if there wasnt a date in column
A the cell would be blank, that way when the formula is copied down the
column you wont have any results displayed for rows that dont have a date:

=IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays))
Hope this hasnt made things worse, but if it has Ill check back later to
see what the next post has to say.

--
Kevin Backmann


"DRondeau" wrote:

Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the
information it seems as though I need to have a definite start date and end
date. Is there a way around this to keep a continuous worksheet? If I have
to put dates how far out do the dates go? Say a year or longer? And to
enter the "holidays" do I just create a new column and list one date on each
row?

"Kevin B" wrote:

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Date formulas

Hi Kevin,

I know I must seem really clueless but I just can't seem to make this work.
If I have the worksheet set up with the date being entered in row G1
(hopefully don't need to specify row# because I would like to keep it as a
rolling worksheet) and to utilize the "IF" formula could you show me the
formula. I just can't seem to figure out what I'm missing. How do I set the
formula for the entire column or is this not possible? I do have a row
headings which I do not want to change. I have created another spreadsheet
for my "Holidays" and would like to have this expand as I need to add other
holidays. Is there a way to set up the formula for this? I am just not able
to figure this out without getting more specific to my own scenario. I hope
you don't mind. Thanks so much for all of your help so far...I truly
appreciate it!

"Kevin B" wrote:

Go to a blank worksheet and in a column of your choosing enter in the
holidays, 1 per row. The dates below are the holidays of the company Im
working for.
9/4/2006
10/09/2006
11/23/2006
11/24/2006
12/25/2006
12/26/2006

You can extend the number of holidays out as far as you wish and when all
holidays have been entered, click and drag from the first holiday date to the
last holiday date to select them all.
Then click INSERT on the menu, select NAME and then DEFINE and enter the
name Holidays and click OK.
I did not read your prior post close enough and the NETWORKDAYS is for
calculating the number of work days between 2 dates, what you want is the
WORKDAY function.
For example, if A1 has a date and I want to calculate 4 working days from
that date the and display the result in column B, the formula would be:
WORKDAY(A1,4, Holidays)

Or you could do the following:
Col A Col B Col C
9/5/2006 6 WORKDAY(A1,B1, Holidays)

You could also use and IF function so that if there wasnt a date in column
A the cell would be blank, that way when the formula is copied down the
column you wont have any results displayed for rows that dont have a date:

=IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays))
Hope this hasnt made things worse, but if it has Ill check back later to
see what the next post has to say.

--
Kevin Backmann


"DRondeau" wrote:

Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the
information it seems as though I need to have a definite start date and end
date. Is there a way around this to keep a continuous worksheet? If I have
to put dates how far out do the dates go? Say a year or longer? And to
enter the "holidays" do I just create a new column and list one date on each
row?

"Kevin B" wrote:

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default Date formulas

Question: Are you entering in a single date into cell G1 and then
calculating date x number of of working days, or is column G going to have
dates down x number of rows?

I'm not sure of how you want to use this formula.

If the dates are posted on Sheet1, with the holidays in Sheet2, does Sheet1
look like the following? (G1 is the starting date, H1 is the number of
working days and I1 is the result of the WORKDAY formula.
G H I
10/24/2006 5 10/24/2006

And does Sheet2 resemble the following:
A
1 09/04/2006
2 10/11/2006
3 11/23/2006
4 11/24/2006
5 12/25/2006
6 12/26/2006

The formula I1 would be =WORKDAY(G1,H1,Sheet2!A1:A6)

If you're going to have multiple dates you would enter the formula in I1
with an absolute reference to the holidays in Sheet 2:

The formula I1 would be =WORKDAY(G1,H1,Sheet2!$A$1:$A$6) and then you could
copy the formula down column I. The worksheet would look like this:
Column A Colum B Column C
1 10/17/2006 5 10/24/2006
2 11/07/2006 10 11/21/2006
3 12/23/2006 8 01/05/2007

The dollar signs in the reference to the holidays prevent Excel from
adjusting the formula by 1 row each time its copied down column I
--
Kevin Backmann


"DRondeau" wrote:

Hi Kevin,

I know I must seem really clueless but I just can't seem to make this work.
If I have the worksheet set up with the date being entered in row G1
(hopefully don't need to specify row# because I would like to keep it as a
rolling worksheet) and to utilize the "IF" formula could you show me the
formula. I just can't seem to figure out what I'm missing. How do I set the
formula for the entire column or is this not possible? I do have a row
headings which I do not want to change. I have created another spreadsheet
for my "Holidays" and would like to have this expand as I need to add other
holidays. Is there a way to set up the formula for this? I am just not able
to figure this out without getting more specific to my own scenario. I hope
you don't mind. Thanks so much for all of your help so far...I truly
appreciate it!

"Kevin B" wrote:

Go to a blank worksheet and in a column of your choosing enter in the
holidays, 1 per row. The dates below are the holidays of the company Im
working for.
9/4/2006
10/09/2006
11/23/2006
11/24/2006
12/25/2006
12/26/2006

You can extend the number of holidays out as far as you wish and when all
holidays have been entered, click and drag from the first holiday date to the
last holiday date to select them all.
Then click INSERT on the menu, select NAME and then DEFINE and enter the
name Holidays and click OK.
I did not read your prior post close enough and the NETWORKDAYS is for
calculating the number of work days between 2 dates, what you want is the
WORKDAY function.
For example, if A1 has a date and I want to calculate 4 working days from
that date the and display the result in column B, the formula would be:
WORKDAY(A1,4, Holidays)

Or you could do the following:
Col A Col B Col C
9/5/2006 6 WORKDAY(A1,B1, Holidays)

You could also use and IF function so that if there wasnt a date in column
A the cell would be blank, that way when the formula is copied down the
column you wont have any results displayed for rows that dont have a date:

=IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays))
Hope this hasnt made things worse, but if it has Ill check back later to
see what the next post has to say.

--
Kevin Backmann


"DRondeau" wrote:

Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the
information it seems as though I need to have a definite start date and end
date. Is there a way around this to keep a continuous worksheet? If I have
to put dates how far out do the dates go? Say a year or longer? And to
enter the "holidays" do I just create a new column and list one date on each
row?

"Kevin B" wrote:

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Date formulas

Just to clarify...Row 1 on my sheet is a column heading - does this matter?
The date in Column G will be different for most entries on spreadsheet. It
will be dependent upon when the information is added. Each row will be a new
date entered. The # of working days is in Column H and Column I is the
result.

I seem to have made the formula work on my first row - which is very
exciting! Now I need to copy this down the entire column I.

For the Holidays - I have them set up on sheet 2 in column A starting at row
1. I entered the formula for Column I as =WORKDAY (G2,H2,SHEET2!$A$1:$A$4) I
only have 4 holidays listed so far. If I happen to add more dates will I
need to change the formula in Column I to reflect this? How would I do it if
I need to?

I also wanted to find out if there is a way to create the formula so it
won't show a date in Column I without having the information yet in Column G?

When you say format the cell as DATE I need to use Insert - Function - Date
or is there an easier way to format the entire Column as such? With the
Insert function it seems I need to do this for each individual row (cell).
Hopefully there is an easier way.

I hope this answers the questions you had and hopefully didn't create more
confusion...I have a tendency to do that! :)

Thanks,
Dione
"Kevin B" wrote:

Question: Are you entering in a single date into cell G1 and then
calculating date x number of of working days, or is column G going to have
dates down x number of rows?

I'm not sure of how you want to use this formula.

If the dates are posted on Sheet1, with the holidays in Sheet2, does Sheet1
look like the following? (G1 is the starting date, H1 is the number of
working days and I1 is the result of the WORKDAY formula.
G H I
10/24/2006 5 10/24/2006

And does Sheet2 resemble the following:
A
1 09/04/2006
2 10/11/2006
3 11/23/2006
4 11/24/2006
5 12/25/2006
6 12/26/2006

The formula I1 would be =WORKDAY(G1,H1,Sheet2!A1:A6)

If you're going to have multiple dates you would enter the formula in I1
with an absolute reference to the holidays in Sheet 2:

The formula I1 would be =WORKDAY(G1,H1,Sheet2!$A$1:$A$6) and then you could
copy the formula down column I. The worksheet would look like this:
Column A Colum B Column C
1 10/17/2006 5 10/24/2006
2 11/07/2006 10 11/21/2006
3 12/23/2006 8 01/05/2007

The dollar signs in the reference to the holidays prevent Excel from
adjusting the formula by 1 row each time its copied down column I
--
Kevin Backmann


"DRondeau" wrote:

Hi Kevin,

I know I must seem really clueless but I just can't seem to make this work.
If I have the worksheet set up with the date being entered in row G1
(hopefully don't need to specify row# because I would like to keep it as a
rolling worksheet) and to utilize the "IF" formula could you show me the
formula. I just can't seem to figure out what I'm missing. How do I set the
formula for the entire column or is this not possible? I do have a row
headings which I do not want to change. I have created another spreadsheet
for my "Holidays" and would like to have this expand as I need to add other
holidays. Is there a way to set up the formula for this? I am just not able
to figure this out without getting more specific to my own scenario. I hope
you don't mind. Thanks so much for all of your help so far...I truly
appreciate it!

"Kevin B" wrote:

Go to a blank worksheet and in a column of your choosing enter in the
holidays, 1 per row. The dates below are the holidays of the company Im
working for.
9/4/2006
10/09/2006
11/23/2006
11/24/2006
12/25/2006
12/26/2006

You can extend the number of holidays out as far as you wish and when all
holidays have been entered, click and drag from the first holiday date to the
last holiday date to select them all.
Then click INSERT on the menu, select NAME and then DEFINE and enter the
name Holidays and click OK.
I did not read your prior post close enough and the NETWORKDAYS is for
calculating the number of work days between 2 dates, what you want is the
WORKDAY function.
For example, if A1 has a date and I want to calculate 4 working days from
that date the and display the result in column B, the formula would be:
WORKDAY(A1,4, Holidays)

Or you could do the following:
Col A Col B Col C
9/5/2006 6 WORKDAY(A1,B1, Holidays)

You could also use and IF function so that if there wasnt a date in column
A the cell would be blank, that way when the formula is copied down the
column you wont have any results displayed for rows that dont have a date:

=IF(ISBLANK(A1),"",WORKDAY(A1,B1,Holidays))
Hope this hasnt made things worse, but if it has Ill check back later to
see what the next post has to say.

--
Kevin Backmann


"DRondeau" wrote:

Thank you for responding so quickly! :) I am not very proficient with Excel
so excuse me if I seem ignorant...when I go into Help and pull up the
information it seems as though I need to have a definite start date and end
date. Is there a way around this to keep a continuous worksheet? If I have
to put dates how far out do the dates go? Say a year or longer? And to
enter the "holidays" do I just create a new column and list one date on each
row?

"Kevin B" wrote:

In help lookup the NETWORKDAYS function.

NETWORKDAY(Date1, Date2, [Optional Holidays])

You can create a named range of all your company holidays and then use the
range name as the argument value for holidays.

If you get a #NAME error you need to go to TOOLS/ADD INS and check off the
ANALYSIS TOOLPACK
--
Kevin Backmann


"DRondeau" wrote:

I am trying to find a formula or function that will enable me to add 4 work
days (Mon-Fri) to a date entered in another column. Dates will change. Also
need to figure out how to format the range for Holidays in order for those to
not be counted as well. I am not quite sure how to format the WORKDAY
function if this is the appropriate choice. Any help would be most
appreciated. I've been working on this for the past week and have had no
luck. Thanks!

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
Mileage Claim Formula johndavies New Users to Excel 4 August 14th 06 09:24 AM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Date formulas needed David Excel Discussion (Misc queries) 9 July 24th 05 02:01 AM
Min/Max formulas using cells with date format WDS2000 Excel Worksheet Functions 1 February 7th 05 07:03 PM
Using formulas to determine date in one cell based on date in anot Gary Excel Worksheet Functions 2 November 22nd 04 09:11 AM


All times are GMT +1. The time now is 09:01 PM.

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"