Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
I want to know what is the date after no. of working days.
Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
Use the WORKDAY function:
=WORKDAY(start_date,days) or you can add a third parameter relating to holidays. You need to install the Analysis ToolPak for this to work if you are using Excel 2003 or earlier - see Excel Help for further details. Hope this helps. Pete On Oct 3, 4:32*pm, sudheer wrote: I want to know what is the date after no. of working days. Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
Give the following function a try...
Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date If WorkDays < 0 Then Exit Function AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _ 2 * ((WorkDays Mod 5) Abs(5 + - _ Weekday(StartDate, vbMonday))) + _ Weekday(StartDate, vbSaturday) * _ (Weekday(StartDate, vbSaturday) < 3), StartDate) End Function As written, this function will only *add* workdays to the StartDate. If the WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and can be used for error checking purposes. -- Rick (MVP - Excel) "sudheer" wrote in message ... I want to know what is the date after no. of working days. Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
It just occurred to me that my solution (a VB coded solution) is probably
not what you were looking for. I had just posted some answers over in the programming newsgroup and forgot that I was not still in that newsgroup when I posted my answer to you. I think Pete's answer is the one you are looking for. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give the following function a try... Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date If WorkDays < 0 Then Exit Function AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _ 2 * ((WorkDays Mod 5) Abs(5 + - _ Weekday(StartDate, vbMonday))) + _ Weekday(StartDate, vbSaturday) * _ (Weekday(StartDate, vbSaturday) < 3), StartDate) End Function As written, this function will only *add* workdays to the StartDate. If the WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and can be used for error checking purposes. -- Rick (MVP - Excel) "sudheer" wrote in message ... I want to know what is the date after no. of working days. Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
I thought that was a bit OTT, Rick !! <bg
Pete On Oct 3, 5:12*pm, "Rick Rothstein" wrote: It just occurred to me that my solution (a VB coded solution) is probably not what you were looking for. I had just posted some answers over in the programming newsgroup and forgot that I was not still in that newsgroup when I posted my answer to you. I think Pete's answer is the one you are looking for. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give the following function a try... Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date *If WorkDays < 0 Then Exit Function *AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _ * * * * * * * * * * * *2 * ((WorkDays Mod 5) Abs(5 + - _ * * * * * * * * * * * *Weekday(StartDate, vbMonday))) + _ * * * * * * * * * * * *Weekday(StartDate, vbSaturday) * _ * * * * * * * * * * * *(Weekday(StartDate, vbSaturday) < 3), StartDate) End Function As written, this function will only *add* workdays to the StartDate. If the WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and can be used for error checking purposes. -- Rick (MVP - Excel) "sudheer" wrote in message ... I want to know what is the date after no. of working days. Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
Well, maybe a little. However, if the OP doesn't want to use the Analysis
ToolPak for some reason, or if he is afraid that users he distributes the worksheet to might not have it turned on, he can always place my function in a Module and then use it as a User Defined Function (UDF) directly on the worksheet. -- Rick (MVP - Excel) "Pete_UK" wrote in message ... I thought that was a bit OTT, Rick !! <bg Pete On Oct 3, 5:12 pm, "Rick Rothstein" wrote: It just occurred to me that my solution (a VB coded solution) is probably not what you were looking for. I had just posted some answers over in the programming newsgroup and forgot that I was not still in that newsgroup when I posted my answer to you. I think Pete's answer is the one you are looking for. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give the following function a try... Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date If WorkDays < 0 Then Exit Function AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _ 2 * ((WorkDays Mod 5) Abs(5 + - _ Weekday(StartDate, vbMonday))) + _ Weekday(StartDate, vbSaturday) * _ (Weekday(StartDate, vbSaturday) < 3), StartDate) End Function As written, this function will only *add* workdays to the StartDate. If the WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and can be used for error checking purposes. -- Rick (MVP - Excel) "sudheer" wrote in message ... I want to know what is the date after no. of working days. Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i know date after no. of working days
and I've learned yet another piece of code. I could be a certified VB
programmer in about 3 months just by reading the posts! "Rick Rothstein" wrote: Well, maybe a little. However, if the OP doesn't want to use the Analysis ToolPak for some reason, or if he is afraid that users he distributes the worksheet to might not have it turned on, he can always place my function in a Module and then use it as a User Defined Function (UDF) directly on the worksheet. -- Rick (MVP - Excel) "Pete_UK" wrote in message ... I thought that was a bit OTT, Rick !! <bg Pete On Oct 3, 5:12 pm, "Rick Rothstein" wrote: It just occurred to me that my solution (a VB coded solution) is probably not what you were looking for. I had just posted some answers over in the programming newsgroup and forgot that I was not still in that newsgroup when I posted my answer to you. I think Pete's answer is the one you are looking for. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give the following function a try... Function AddWorkDays(StartDate As Date, WorkDays As Long) As Date If WorkDays < 0 Then Exit Function AddWorkDays = DateAdd("d", 7 * (WorkDays \ 5) + (WorkDays Mod 5) - _ 2 * ((WorkDays Mod 5) Abs(5 + - _ Weekday(StartDate, vbMonday))) + _ Weekday(StartDate, vbSaturday) * _ (Weekday(StartDate, vbSaturday) < 3), StartDate) End Function As written, this function will only *add* workdays to the StartDate. If the WorkDays value is less than 0, then "day zero" (12/30/1899) is returned and can be used for error checking purposes. -- Rick (MVP - Excel) "sudheer" wrote in message ... I want to know what is the date after no. of working days. Suppose if we consider today's date i have to get date after 12 wrkng days . Today's date:3-oct-08 Date aftr 12 wrkng days :21-oct-08(Result) I need formula to execute that and how to implement it in shared workbooks ?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working out days between date. | Excel Worksheet Functions | |||
Can Excel add working days to a date to result in another date? | Excel Worksheet Functions | |||
How do you add 40 working days to a date | Excel Discussion (Misc queries) | |||
Add on 8 working days to a date | Excel Worksheet Functions | |||
date (minus) date = working days diff | Excel Discussion (Misc queries) |