Date help required in Excel IF/DO

**kwaka72** · 01-26-2012, 11:53 PM

Hello,
I am updating a daily checklist and wish to have the date automatically update when we print it off each day.
I know the =today() command will do this.
However, on the weekends we print off one checklist for Saturday and Sunday combined.
How can I set it up so that on mon -fri I get the current days date, but if it is a Saturday, I want Saturdays and Sundays date both
shown on the checklist.

Thanks!
Lee

**NickyC** · 01-27-2012, 12:19 AM

I dare say one of the forum gurus will have a more economical solution, but this should work
you can insert your preferred date format in place of "dd mmm yy" if you like

=IF(WEEKDAY(TODAY())=7,TEXT(TODAY(),"dd mmm yy")&" and "&TEXT(TODAY()+1,"dd mmm yy"),IF(WEEKDAY(TODAY())=1,TEXT(TODAY()-1,"dd mmm yy")&" and "&TEXT(TODAY(),"dd mmm yy"),TODAY()))

**kwaka72** · 01-27-2012, 01:25 AM

Thanks Nicky,
Can I ask if I want a full date eg Saturday, 28 January 2012 format, what should I put in place of the "dd mm yy" you have used in your example?
I am testing your suggestion, and so far I got it to work with my preferred format just by format cells/date and specifying it in there.

**NickyC** · 01-27-2012, 02:08 AM

Hi
formatting the cells will work fine for dates that are also numbers, but when you change the cell content to text (necessary to include two separate dates and "and " in one cell) you will need to specify the number format of each date separately.

for Friday, 27 January 2012 use

"dddd, dd mmmm yyyy"

**kwaka72** · 01-28-2012, 12:03 PM

this has worked a treat! Thanks Nicky.
I have come across another issue though. On the weekend checklist (with the 2 dates), I require the dates to be one above the other. This is because I have run out of room having the 2 dates on the same line. Is there a way to make a new line? (in the same cell is ok). I have editted Nicky's code a little to change the date format. Here it is:
=IF(WEEKDAY(TODAY())=7,TEXT(TODAY(),"dddd, dd mmmm yyyy")&" and "&TEXT(TODAY()+1,"dddd, dd mmmm yyyy"),IF(WEEKDAY(TODAY())=1,TEXT(TODAY()-1,"dddd, dd mmmm yyyy")&" / "&TEXT(TODAY(),"dddd, dd mmmm yyyy"),TODAY()))

**NickyC** · 01-29-2012, 12:34 AM

Hi
you can do this using Char(10), which includes a carriage return in your formula - but it only works if the cell's format is set to "wrap text".

Try this:

=IF(WEEKDAY(TODAY())=7,TEXT(TODAY(),"dddd, dd mmmm yyyy")&"/"&CHAR(10)&TEXT(TODAY()+1,"dddd, dd mmmm yyyy"),IF(WEEKDAY(TODAY())=1,TEXT(TODAY()-1,"dddd, dd mmmm yyyy")&"/"&CHAR(10)&TEXT(TODAY(),"dddd, dd mmmm yyyy"),TODAY()))