Gantt chart,workday function include weekend and somebug

[zophos]

Problem (in short)
I had an excel gantt chart but when I use WORKDAYS function it doesnot include saturday and sunday how can i make them included

Problem (Long)

I've obtained a gantt chart (from http://www.youtube.com/watch?v=hxrX8qTSnOc) and I've modify very little thing inside ex.

1. Day needed column for using in workday function
2. If the start date is holiday or sunday or hasn't type yet the cell will show in green

It's ashamed that all i can do is only 2 things and the following bug is that i can't solve for a week

Bug no.1
1. in this excel workday function does not include weekend
but sometime we work on Saturday too
Bug no.2
2. In "END" column it will show the last date of holiday
if work is done on the day before the holiday
i.e. start wednesday use 3 workdays in will show Sunday instead of Friday


Possible solution (in my opinion)
bug no1
I needed to make the WORKDAY function include Saturday and I will type the Saturday's date that we work in Holiday column
Bug no.2
I had some idea from website but still, I don't quite get it. It's on worksheet "Bug no.2"

Thankyou for any suggestion and sorry for my poor english

link of excel file
http://www.2shared.com/document/3qKhpZVZ/Gantt_Rev_02_use_for_web.html

Thankyou very much again!!!

 

[GlennUK]

I cannot see you link due to security policy at work here. But I can say that WORKDAY is a function to use if you want to skip weekends .... that's the whole point of using it ... it's not a bug. If you want a date generated from another date simply add then number of days onto the original date.

 

[zophos]

Thankyou very much for your response
I've try to upload some picture to show the excel instead

How can I count that how many Saturday,Sunday and holiday From Start column to end column
Example
1.In Task number 5 there is 4 Holiday(25,30 is some public holiday and 27,28 is Saturday and Sunday)
How can I make them count how many holiday From start date (G18) to end date(H18). I cannot think of Logic like COUNTIF(From date in cell G18 to Date in cell H18 = Sat or Sun or in holiday column)
2.In task 4 END date in H17 show 30/9 (which is Holiday) Instead of 29/9
Do you have any Idea About this

Formula
G18
No Formula
H18
=WORKDAY(G18,F18,D$8:D$10)-1
D18
Is what I'm looking for

Condition Formatting
I18
Show Green=AND(AND(I$13>=$G18,I$13<=$H18),OR(I$13=$D$8:$D$10,WEEKDAY(I$13,2)>5))

Show Red=AND(I$13>=$G18,I$13<=$H18)

Thankyou very much for any suggestion

 

[GlennUK]

Can you explain why you are subtracting 1 from your WORKDAY function result?

To count the number of Sat , Sun and Holidays in a date range, just use the NETWORKDAYS function to count the number of non-holiday/weekend dates, and subtract that from the number of days in the range.

 

[zophos]

Thankyou very much!!! GlennUK
You are the man!!!
I can't think of using networkday

For the [-1] in workday function
in my opinion when "day needed" column is 1 I mean that this task will start and end at that day. If one day mean start today end tomorrow then you do not need [-1] in workday condition

For the second problem(End date is holiday) I will make the column green if it is holiday or weekend so that the user can aware the end date and try to figure the way out after that but, Again, Thankyou very much

 

[GlennUK]

Instead of taking 1 off after the calculation, why not take the 1 off the amount to move ... like in:
=WORKDAY(G18,F18-1,D$8:D$10)
?

 

[zophos]

Because at the "End" column will show the error date for the cell that I havn't put the value in Start and Day needed since i set the format in that column to "dd,mm" and if i do so, it will show 0/1 instead of #### which easier to see or may be i could use some if function but I just don't want to put too much formular in a cell

thankyou for your suggesting again

 

[GlennUK]

Maybe something like:

=IF(G18="","",WORKDAY(G18,F18-1,D$8:D$10))