1

I want to make cell A with yellow when the date from this cell is less with two days than today and only when cell C or B are empty, and cell A to be with red when the date is less with two days than today and both cells C and B are empty. What formula should i use in conditional formatting ?

This formula is working : if(and(a1<=today()-2,b1128=""),1,0) But i would like if its possible to calculate only the working days. Could someone give me a idea ?

@Emily : 7/5/2021 - 2 working days = 7/1/2021. So i need to mark only the 7/1/2021 and the oldest dates in this case. In this -2 days i want to add also the weekend days if is the case ... For eg. 7/2/2021 to be marked only in 7/6/2021

if the date is on Friday i want to be marked only on Tuesday... To skip the weekend days. If is on Monday to be marked on Wednesday

@Emily : the last one's are not working at all, i dont know why ... Actually is working only for 7/2/2021

@Emily : Yes, in your table is the correct information just with mention that i need to mark also the older dates : eg. On Monday i need to mark last Wednesday and all dates that are older than last Wednesday ... On Tuesday i need to mark last Thursday and all dates that are older than last Thursday ... And so on ...

Improve this question
4
  • You should provide an numerical example. What have you tried? The AND command allows you to achieve multiple test conditions.
    – StainlessSteelRat
    Commented Jul 4, 2021 at 21:31
  • Hi @PRADAN ADRIAN ,, if you wanna to respond any of the posts then use comments rather than add your views with original post !!
    – Rajesh Sinha
    Commented Jul 6, 2021 at 6:11
  • Check your post & better edit since for both RED & Yellow you have same criteria "date is less with two days than today and both cells C and B are empty" !!
    – Rajesh Sinha
    Commented Jul 6, 2021 at 6:21
  • Yellow when C or B and red when both of them are empty and yea, same criteria less with two days then today and to skip the weekend days ( if its friday to be marked only on tuesday )
    – PRADAN ADRIAN
    Commented Jul 6, 2021 at 6:37

1 Answer 1

Reset to default
1

Thanks for your explaination, I would update my reply as following.

According to your description, it seems that you need to get following results. Any misunderstanding, you may tell me.

enter image description here

Highlight cell A with Red：

=IF(WEEKDAY(TODAY(),2)>3,AND(A1<=TODAY()-3,B1&C1=""),AND(A1<=TODAY()-5,B1&C1=""))

Highlight cell A with Yellow:

=IF(WEEKDAY(TODAY(),2)>3,AND(A1<=TODAY()-3,B1<>"",C1=""),AND(A1<=TODAY()-5,B1<>"",C1=""))

=IF(WEEKDAY(TODAY(),2)>3,AND(A1<=TODAY()-3,B1="",C1<>""),AND(A1<=TODAY()-5,B1="",C1<>""))

Please also pay attention to the application range of these formulas.

enter image description here

Improve this answer
10
  • From you eg. 7/2/2021 should be not with yellow because 7/5/2021 - 2 working days = 7/1/2021. So i need to mark only the 7/1/2021 and the oldest dates in this case. In this -2 days i dont want to count the weekend days ... For eg. 7/2/2021 to be marked only in 7/6/2021
    – PRADAN ADRIAN
    Commented Jul 5, 2021 at 14:08
  • The point is that if the date is on Friday i want to be marked only on Tuesday... To skip the weekend days. If is on Monday to be marked on Wednesday
    – PRADAN ADRIAN
    Commented Jul 5, 2021 at 15:25
  • @PRADANADRIAN You need to add your comments to the original question.
    – StainlessSteelRat
    Commented Jul 5, 2021 at 17:24
  • Thank you but these formulas are working only for 7/2/2021
    – PRADAN ADRIAN
    Commented Jul 6, 2021 at 7:59
  • @PRADANADRIAN Does this "two days" include "today"? One example " 7/5/2021 - 2 working days = 7/1/2021. So i need to mark only the 7/1/2021", "two days" here includes the day of 7/5/2021. But another one "If is on Monday to be marked on Wednesday", it excludes the day of Monday self. If based on this description, 7/5/2021 - 2 working days = 6/30/2021.
    – Emily
    Commented Jul 6, 2021 at 8:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .