weekend and weekday formulas

[oxcor]

Hello,
I have a problem with WEEKEND and WEEKDAY formulas. I am trying 2 different DATE formats on my sheet, but I couldn't choose the exact weekend days. I am sending some screenshots about my problem. I have tried many times, but formula takes the wrong data format and colors the weekend days differently.

Here I used one USA data format and the second is Turkish date format. You can see the dates top in the command line. Both of them selects the wrong weekend days. And some cells haven't date, it colors it too. And when I use WEEKEND and WEEKDAY formulas with input like this $D$10, it doesn't do anything. When I write it like this $D10, it colors some of them. But not right. It selects that if the date 11/03/2017, March 11, 2017. But I have tried it with Turkish date format too.

I don't understand why it is like that? Does anyone understand my problem and where did I wrong?

Thanks for your interest.

[oxcor]

here are the date formats.

[oxcor]

This is what I see.

[leelnich]

Can you upload a sample workbook using the problem data? (so we don't have to waste time re-creating your spreadsheet)

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now click Browse, find your file, then click Upload. Simple!

[oxcor]

I have sent it.

[Root_]

Try

=AND(WEEKDAY($C1,2)>5,$C1<>"")

[oxcor]

thanks for your interest. that works. could you please tell me why that happened? what was my problem? and how did you solve this problem? I just want to understand my problem. thanks a lot.

[Root_]

You applied a CF rule, which references $C4, to range $C$1:$C$xx. As a result, you got a 3-row shift.

The $C1<>"" part is needed to avoid highlighting blank cells.

[oxcor]

great! thanks for your explanation. have a nice day.

[leelnich]

=AND(WEEKDAY($C1,2)>5,$C1<>"")

This works for column C (by ignoring empty cells), but column D has additional problems. Some of the entries are numbers, others are text that looks like numbers. Excel mistakenly assigns a datevalue of 0 (= 1/0/1900 = a Saturday!) to text and blanks, so the text must be converted. Select Column D, then click Text to Columns on the Data tab. Select Fixed Width, then click NEXT twice to move to Step 3 of the wizard (Data Format). Select Date, then click the drop-down arrow and select DMY. Now click Finish. All text should now be numbers, which Excel can handle properly.

ps, you must also insure that the Conditional Formatting formula for column D aligns with its Applies to range. If the first cell is D4, then the formula must read =WEEKDAY($D4,2)>5.

[oxcor]

thansk a lot guys. that formula worked for me.

[leelnich]

Happy to help! If that concludes your thread, please got to Thread Tools (up top) and mark it as SOLVED. Thanks! - Lee

[Root_]

@ leelnich:

Just to mention: WEEKDAY works for both dates-as-numbers and dates-as-strings.
For non-date strings, it returns a #VALUE!. So, there was no need to convert anything.

[leelnich]

...For non-date strings, it returns a #VALUE!...

- and therein lies the problem. It's not about the formula, it's about how Excel struggles with the D/M/Y date format, even though the cells in question have a Regional number format that supports it. Any date that falls after the 12th of a month is incorrectly treated as straight text, not as a date, so the CF formula FAILS for them. Please see the attached workbook for clarification of the issue.

[daddylonglegs]

Hello Lee,

I suspect this will depend on regional settings. Conditional formatting works for me in your attachment , e.g. D17 is formatted as expected because 16th September 2017 was a Saturday......but my default date format is dd/mm/yyyy so, as Root_ says WEEKNUM will correctly interpret that as a date (even when text formatted) assuming it's a valid date based on your regional settings.

I understand that Ukraine also uses dd/mm/yyyy so it might work OK for oxcor too

[leelnich]

Noted! I've seen a lot of threads about formatting issues (especially dates) complicated by different regional settings. I usually use "11 Nov 2017" to save on confusion. For the sake of completeness, here's a screenshot showing the data as I saw it.Untitled.png

[oxcor]

thanks for your explanation. that's all clear now.