| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Function
I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
try this,
Add 3 working days to a1. Holidays is a named range that contains any national holidays =WORKDAY(A1,3,Holidays) Mike "HARSHAWARDHAN. S .SHASTRI" wrote: I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Look at the WORKDAY function, you obviously need to create a list of public
holidays and incorporate it in the formula =WORKDAY(A1,3,holidays) where A1 is the date, 3 is the number of days and holidays can be a named range with public holidays or just a range e.g. H2:H10 Unless you are using Excel 2007 WORKDAY is not installed by default but it comes with the Excel/Office CD so if you get a name error go to toolsadd-ins and select Analysis ToolPak and follow the instructions (keep the CD handy) -- Regards, Peo Sjoblom "HARSHAWARDHAN. S .SHASTRI" wrote in message ... I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
It does not works.Showing #NAME?.This formula (Workday) is not there in
function list. Harshawardhan Shastri ================================================== ========= "Mike H" wrote: try this, Add 3 working days to a1. Holidays is a named range that contains any national holidays =WORKDAY(A1,3,Holidays) Mike "HARSHAWARDHAN. S .SHASTRI" wrote: I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
You need Analysis ToolPak installed, see my answer to you
-- Regards, Peo Sjoblom "HARSHAWARDHAN. S .SHASTRI" wrote in message ... It does not works.Showing #NAME?.This formula (Workday) is not there in function list. Harshawardhan Shastri ================================================== ========= "Mike H" wrote: try this, Add 3 working days to a1. Holidays is a named range that contains any national holidays =WORKDAY(A1,3,Holidays) Mike "HARSHAWARDHAN. S .SHASTRI" wrote: I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
I can heartily recommend the use of the Excel help function. Type in the
word WORKDAY, and see what it tells you. -- David Biddulph "HARSHAWARDHAN. S .SHASTRI" wrote in message ... It does not works.Showing #NAME?.This formula (Workday) is not there in function list. Harshawardhan Shastri ================================================== ========= "Mike H" wrote: try this, Add 3 working days to a1. Holidays is a named range that contains any national holidays =WORKDAY(A1,3,Holidays) Mike "HARSHAWARDHAN. S .SHASTRI" wrote: I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
It didn't look as though the OP wanted to skip weekend dates, but we shall
await a response. -- David Biddulph "Mike H" wrote in message ... try this, Add 3 working days to a1. Holidays is a named range that contains any national holidays =WORKDAY(A1,3,Holidays) Mike "HARSHAWARDHAN. S .SHASTRI" wrote: I have a work sheet in which column A is having "Recd On" (Date). column B is having "Dispatch On" (Date). If there is no holiday Dispatch On date = Recd On Date +3 . Column H is having list of dates on which there is holiday. What i want is a formula which will give me Dispatch Date which is not a holiday. For example if recd date is 24/09/2008 and there is holiday on 27/9/08,28/9/08,30/9/08 then Dispatch Date should be 01/10/08. Harshawardhan.Shastri India |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date function in ACCRINTM requires date format not available | Excel Worksheet Functions | |||
| MAX figure within a date range as a function of today()'s date | Excel Worksheet Functions | |||
| SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
![HARSHAWARDHAN. S .SHASTRI[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
