Week Ending Date based off cell with formula

[pup33pawz]

Hello!

I am trying to formulate a week ending date based off of a cell that contains a formula. For example: C1 contains =IF(B1 = "Sara",TODAY(),""). This returns today's date. I would like D1 to calculate the week ending date based off of the date generated on C1. Is this possible? I've tried =(7-WEEKDAY(C1,11))+C1 but that returns #Value error

 

[mole999]

Try this for Sunday =(7-WEEKDAY(C1,2))+C1

 

[pup33pawz]

Hi mole999,

This formula returns #Value error, I believe because C1 has a formula in it already.

 

[mole999]

is B1 = to sara ?

 

[pup33pawz]

B1: Sara
C1: =IF(B1 = "Sara",TODAY (),"")
D1: Need week ending formula based on date of C1

=(7-WEEKDAY(C1,2))+C1 returns #Value error, I think because C1 has formula in it

 

[mole999]

I have
B1: sara
C1: =IF(B1 = "Sara",TODAY(),"")
D1: =(7-WEEKDAY(C1,2))+C1

and that works until B1 is blank

problem with this is you are limited to sara, and next week it will still report today being on that week

 

[Jonmo1]

B1: Sara
C1: =IF(B1 = "Sara",TODAY (),"")
D1: Need week ending formula based on date of C1

=(7-WEEKDAY(C1,2))+C1 returns #Value error, I think because C1 has formula in it

The only reasonable explaination for that formula to return #Value! is if B1 does NOT = "Sara"
If B1 does NOT = "Sara", then C1 = ""
And Weedkay("",2) = #Value! (it expects a date value, not a text string "")

Check B1 for extra spaces, like "Sara " or " Sara" instead of "Sara"

What does this return
=B1="Sara"
?

 

[mole999]

we could suggest =IF(TRIM(B1) = "Sara",TODAY(),"") to allow for spaces

 

[pup33pawz]

Thank you! I realized, #Value error is showing in cases where column B is blank. The formula you sent works How do I have blank return if Column B is blank?

 

[mole999]

you mean like =IF(B1<>"",(7-WEEKDAY(C1,2))+C1,"BlAnK")