Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rohin Bhatia
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*(ROW(INDIRECT
("1:"&ABS(days)*10))))={2,3,4,5,6})*
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

change the array {2,3,4,5,6} to the weekdays that you want to use as working
days.

It uses three named ranges

start_date - single cell, obvious
days - single cell, the working days to project forward
holidays, range, an array of holiday dates

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" <Rohin wrote in message
...
HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A

WORKING
DAY ??



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Hi

Write an UDF

I posted one as a reply to a thread Workdays from 29.05.2006 14:10 in
microsoft.public.excel newsgroup.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Rohin Bhatia" <Rohin wrote in message
...
HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rohin Bhatia
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say June 1.
How do I arrive at end date by considering 6 working days in the week - Thus
only Sunday is the off day . All other days including Saturday are working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Rohin,

My original formula does exactly what you want, just change the days array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say June

1.
How do I arrive at end date by considering 6 working days in the week -

Thus
only Sunday is the off day . All other days including Saturday are

working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A

WORKING
DAY ??





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rohin Bhatia
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Thanks a ton
Regds
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A WORKING
DAY ??

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rohin Bhatia
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Hi Bob
Sorry - coming back to u again.
The formula given by u works well in the case I gave before.
However if I have a target finish(End) date and know that a task requires
say X days,How do I work backwords to arrive at a start date - again using
same assumption that only Sunday + holiday list are nonworking - All other
days are working days.
Need ur help
Regds
Rohin

"Bob Phillips" wrote:

Rohin,

My original formula does exactly what you want, just change the days array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *
ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say June

1.
How do I arrive at end date by considering 6 working days in the week -

Thus
only Sunday is the off day . All other days including Saturday are

working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A

WORKING
DAY ??




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Just use a negative number of days.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Hi Bob
Sorry - coming back to u again.
The formula given by u works well in the case I gave before.
However if I have a target finish(End) date and know that a task requires
say X days,How do I work backwords to arrive at a start date - again using
same assumption that only Sunday + holiday list are nonworking - All other
days are working days.
Need ur help
Regds
Rohin

"Bob Phillips" wrote:

Rohin,

My original formula does exactly what you want, just change the days

array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *

ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter,

not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say

June
1.
How do I arrive at end date by considering 6 working days in the

week -
Thus
only Sunday is the off day . All other days including Saturday are

working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A

WORKING
DAY ??






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rohin Bhatia
 
Posts: n/a
Default SET SATURDAY AS WORKING DAY IN EXCEL

Dear Bob
Thanks a lot.
Rohin

"Bob Phillips" wrote:

Just use a negative number of days.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Hi Bob
Sorry - coming back to u again.
The formula given by u works well in the case I gave before.
However if I have a target finish(End) date and know that a task requires
say X days,How do I work backwords to arrive at a start date - again using
same assumption that only Sunday + holiday list are nonworking - All other
days are working days.
Need ur help
Regds
Rohin

"Bob Phillips" wrote:

Rohin,

My original formula does exactly what you want, just change the days

array

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_dat e+SIGN(days)*
(ROW(INDIRECT("1:"&ABS(days)*10))))={2,3,4,5,6,7}) *

ISNA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1: "&ABS(days)*10))),holidays
,0)),
ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

which is an array formula, it should be committed with Ctrl-Shift-Enter,

not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rohin Bhatia" wrote in message
...
Dear Bob
Sorry - Im somewhat new to this . Let me clarify my exact problem.
Lets say that a task requires say 20 working days. Starting day say

June
1.
How do I arrive at end date by considering 6 working days in the

week -
Thus
only Sunday is the off day . All other days including Saturday are
working.
Pls help .
Thanks
Rohin

"Rohin Bhatia" wrote:

HOW CAN I CALCULATE WORKDAYS IN EXCEL BY SETTING SATURDAY ALSO AS A
WORKING
DAY ??






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
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
excel 2003 slow when working with excel 2000 files michel Excel Discussion (Misc queries) 0 November 28th 05 04:40 PM
Excel hyperlink not working Jim Excel Discussion (Misc queries) 0 November 28th 05 04:23 PM
print page seprator for mutiple copies in excel is not working RAM Excel Discussion (Misc queries) 0 November 25th 05 03:53 PM
Excel in Internet Explorer Menus quit working Phil Mullins Excel Discussion (Misc queries) 0 April 8th 05 10:15 PM


All times are GMT +1. The time now is 03:36 AM.

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"