If function to exclude weekends

[UMAKEMESIK]

Hello to all.

I have an if statement that works great but I could not figure out how to not included weekends
so my data does not move on weekends.
I tried workdays and other phrases, but I could not figure out
the correct syntax.


Excluding Holidays would be great as well, but not a priority.

would like to start with weekends first.

so my data only moves on workdsays or business days.


i have these statements

=IF( C3=TODAY(), D3, "" )

Next cell to the right

=IF(C3=TODAY()-1, D3, "")

Next cell to the right

=IF(C3=TODAY()-2, D3, "")

And so on, the if statements in each
cell go on for another two days and they
move the data from left to right as needed.

but , what if my original entry starts on a Friday
then i dont want the data to move until Monday
and that would be
this cell

=IF(C3=TODAY()-1, D3, "")

thanks for any help in advance

 

[Joe4]

The WORKDAY function should do what you want.
Here is a write-up on how it works, with examples: How to use the Excel WORKDAY function | Exceljet.
If you are having trouble getting this to work, please post the WORKDAY formula you came up with, and let us the know the expected output you want (in a particular example).

 

[UMAKEMESIK]

This is what i tried to no avail.

if c7 is a date then apply the value in e7 to this cell

=WORKDAY(C7,E7, "" )

and i tried

=WORKDAY(C7,E7)

This is the most basic one


and this
for the nest working day
with no results.

=WORKDAY(C7-1,E7,"")

i am at a loss.

any help would be appreciated.

 

[Joe4]

Can you please walk us through an actual example?
I want to make sure that we have all the correct column references, and see what your expected result is.
What is in Column C?
What is in Column D?
What is in Column E?
What is your expected result of this particular example?

 

[UMAKEMESIK]

start date - column c
( this is the date i enter when i start a project- this date will not change)

project/task - column e
( I enter the name of the project here)

date 1 - column F
(by default

if there is a date in column c and an entry in column e

then take the information in column E and place in column F

so the function in column F is:

=IF( C3=TODAY(), E3, "" )

-------
Day 2 is column G

the date in column c does not change
so after 1 day
the information in column f will move to column G
( this is the code in column g)

=IF(C3=TODAY()-1, E3, "")

and so on....

as i progress farther away from the original date in column c
i just keep adding to the negative number to move the information to the next cell on the right
which is labeled for another day.
and the infomation dissapears from the other cells because of the, "")
at the end.

my problem is that if my first entry date is on a friday.
and i come back in on monday my project will have moved
2 days- which is not reflective of my work environment.

on the weekends i dont want the information to move.

o only want it to move on workdays, business days or weekdays.

 

[Joe4]

See if this does what you want.

Formula in cell F3:
=IF(AND(WORKDAY(C3,0)=WORKDAY(TODAY(),0),E3<>""),E3,"")

Formula in cell G3:
=IF(AND(WORKDAY(C3,0)=WORKDAY(TODAY(),-1),E3<>""),E3,"")

 

[UMAKEMESIK]

Amazing!!!

funny thing is that the fist day does not work.

Formula in cell F3:
=IF(AND(WORKDAY(C3,0)=WORKDAY(TODAY(),0),E3<>""),E3,"")

but my simple code works for the first day
=IF( C3=TODAY(), E3, "" )


but your second code, which is the most important one.
works great.

it skipped the weekend. I entered a date of 8/21 ( last friday)

and my data only moved three cells - 1 for the current day
and the other 2 for Monday and tuesday.


thanks
thanks
thanks.

------------------------------------

if we listed holidays on another sheet, would there be a way to
incorporate Holidays? -

 

[UMAKEMESIK]

oops,

my mistake
your fist day code works perfect.

my error,
sorry

 

[Joe4]

if we listed holidays on another sheet, would there be a way to
incorporate Holidays? -

Yes, just create a list of the Holidays, and include it in the WORKDAYS function as the third argument.
It shows you how to do that in the link I included earlier.
I find it easiest to create the list, and then name the range, and then use the named range as the third argument (you could also use the literal range addresses).

 

[UMAKEMESIK]

I will give it a try.
thanks so much.
if i have any questions, i will let you know.
Have a great day!!!