Nested IFs to output dates excluding Sundays (Med. - Hard difficulty)

Hi Guys!

Small challenge here to test your mad skills.

I want a formula - doesn't need to be an IF statement - that will output dates and their times (Sundays should be excluded from outputs).
Please, please see attached.

Formula should copy to G7 with the result in G7 for highest ratings.
Thank you all so much.

Did you forget the attachment?

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks. I'm afraid I don't understand what I am looking at! How does the table relate to the times top left? What significance do the table headings have? Which cells do you want the formula to create?

OK. I think I have it. The sample data under the months other than August had me confused for a while!

In B7 copied across:

=IF(WEEKDAY(EDATE(B6,0),2)=7,TEXT(EDATE(B6,0)+1,"dd/mm/yyyy"),TEXT(EDATE(B6,0),"dd/mm/yyyy"))&" "&IF(WEEKDAY(EDATE(B6,0),2)=5,TEXT($B$3,"hh:mm"),TEXT($B$2,"hh:mm"))

In B8 copied across and down:

=TEXT(B7+LOOKUP((WEEKDAY(B7,2)),{1,2,3,4,5,6,7},{1,1,1,1,1,2,0}),"dd/mm/yyyy")&" "&IF(WEEKDAY(B7+LOOKUP((WEEKDAY(B7,2)),{1,2,3,4,5,6,7},{1,1,1,1,1,2,0}),2)>5,TEXT($B$3,"hh:mm"),TEXT($B$2,"hh:mm"))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi AliGW, thanks but you are text editing. Sorry for the late reply.
What I want is to use a method which involves evaluating what date it is and making calculations dependent on what date it is.
Please see the attached for the method I have in mind- I need to be able to drag the formula to create more output.

Basically, my formula needs a new structure for its IF statements. Can you please do that?

There was nothing in your original file (which you have replaced with a new one in your opening post this morning) or in your opening post to tell us that a text output would not acceptable. Why did you not include your attempt at the formula in the file you posted first? It would have saved me an hour of unnecessary work yesterday.

Why do you want a repeat of August dates under the other months?

In B7 copied across:

=EDATE(B6,0)+(8.5/24)

In B8 copied across and down:

=IF(WEEKDAY(B7,1)=6,EDATE(B7,0)+1+(9/24),IF(WEEKDAY(B7,1)=7,EDATE(B7,0)+2+(8.5/24),EDATE(B7,0)+1+(8.5/24)))

To make the list stop at the end of the month as you drag down, use this in B8 copied across and down:

=IFERROR(IF(EDATE(B7,0)+1=EDATE(B$6,1),"",IF(WEEKDAY(B7,1)=6,EDATE(B7,0)+1+(9/24),IF(WEEKDAY(B7,1)=7,EDATE(B7,0)+2+(8.5/24),EDATE(B7,0)+1+(8.5/24)))),"")

So, in B7 copied across:

=IF(WEEKDAY(EDATE(B6,0),1)=1,EDATE(B6,0)+1+(8.5/24),EDATE(B6,0)+(8.5/24))

Are we there now?

Not quite! Better make that:

=IF(WEEKDAY(EDATE(B6,0),1)=1,EDATE(B6,0)+1+(8.5/24),IF(WEEKDAY(EDATE(B6,0),1)=7,EDATE(B6,0)+(9/24),EDATE(B6,0)+(8.5/24)))

in case the first day of the month is a Saturday.

Let us know how you get on.

I'll do my best! Give me a moment.

OK, so the formula in B7:

=IF(WEEKDAY(EDATE(B6,0),1)=1,EDATE(B6,0)+1+(8.5/24),IF(WEEKDAY(EDATE(B6,0),1)=7,EDATE(B6,0)+(9/24),EDATE(B6,0)+(8.5/24)))

The section in red uses the EDATE function: EDATE(B6,0) returns the date of B6 at 00:00 hours. (For your understanding, EDATE(B6,1) would return the same date, but one month on.) It then checks if that date in B6 is a Sunday.

If the above is true, the green bit adds one day and 8 and a half hours to the zero-hour date in B6.

The blue bit checks if the date in B6 is a Saturday.

If B6 is a Saturday, the bit in orange adds 9 hours to it.

The final bit of the formula adds 8 and a half hours to the date in B6 if it's not a Sunday or a Saturday.

This makes sure that your start date is correct. I'll break down the second formula in another post.

Now for the formula in B8:

=IFERROR(IF(EDATE(B7,0)+1=EDATE(B$6,1),"",IF(WEEKDAY(B7,1)=6,EDATE(B7,0)+1+(9/24),IF(WEEKDAY(B7,1)=7,EDATE(B7,0)+2+(8.5/24),EDATE(B7,0)+1+(8.5/24)))),"")

The section in red checks if the date in B7 plus one day would be B6 plus one month, and if it is (i.e. if B7 plus one day is September), it returns a blank cell. This stops a result showing once you've reached the end of the month.

The bit in green checks if the weekday of B7 is a Friday, and if it is, it adds one day and 9 hours to it.

The next section in orange checks if B7 is a Saturday, and if it is, it adds two days and 8 and a half hours to it.

The last bit of the formula returns what you would need for any other weekday, i.e. B7 plus one day and 8 and a half hours.

The =IFERROR(...,"") round the whole thing ensures that you don't get a value error message showing once you drag down past the end of the month in that column.

Hope this helps.

I think I might have earned some rep now.

Thanks for the rep!