Weekday1 & 7

[Range]

need help with this formula, basically trying to rep a today as Day 0, yes as day 1, etc. But Sunday & Saturday should not increment.

Currently on a Monday I get

Monday, Day 0
Sunday, Day 0
Saturday Day 0
Friday Day 3 (when it should be day 1).


=IF(WEEKDAY(C32)=1,D33,IF(WEEKDAY(C32)=7,D33,"Day "&($C$33-C32)))

 

[andiemac]

This is probably not the most refined way of doing what you want, but it should get you closer to where you want to be. In my example the column full of dates is A.

=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7,A1=TODAY()),0,-NETWORKDAYS(TODAY()-1,A1,0))

 

[Range]

Oh wow, never used networkdays() before. Will have to try and work that out.

Beofre you responded I sort've worked it out and went with


for Day 0
=IF(WEEKDAY(C33)=1,"",IF(WEEKDAY(C33)=7,"",($C$33-C33)))

For Day 1+
=IF(WEEKDAY(C32)=1,E33,IF(WEEKDAY(C32)=7,E33,(E33+1)))

 

[Scott Huish]

You can simplify that by using the optional parameter for WEEKDAY:

=IF(WEEKDAY(C33,2)>5,"",($C$33-C33))

=IF(WEEKDAY(C32,2)>5,E33,(E33+1))