Date functions in "pure" Excel - Xtreme Visual Basic Talk

06-12-2006, 11:25 AM

blindwig

Ultimate Contributor

* Expert *

Join Date: Jun 2003

Location: New York, NY

Posts: 1,929

Date functions in "pure" Excel

Hey guys, I have a question, not really in VBA, but more in "Pure" Excel -
How do I write a function to give a date on a particular day?
I'm making a sheet for my timecard and one of the fields I need to put is the Pay Period Ending Date. Our pay periods always end on a Saturday, so what I want to do is wrap the Now() function to return the date of the coming Saturday.
So if today is Saturday, I want to know today's date. If today is Friday, I want to know tomorrow's date. If today is Thursday, I want to know the date 2 days from now. Etc...
I'd prefer to do this in "pure" excel, not macros or VBA or whatever.
I started writing a set of nested IFs, then tried a lookup table, and it always got messy and started breaking. Any better ideas?

Thanks,
Mike

__________________
"Fortunately, I live in the United States of America, where we are gradually coming to understand that nothing we do is ever our fault, especially if it is really stupid." -Dave Barry

06-12-2006, 12:55 PM

Wamphyri

Variable not defined

Retired Moderator
* Guru *

Join Date: Apr 2002

Location: Ottawa, Ontario

Posts: 4,793

Yeah, try

Code:

=NOW()+7- WEEKDAY(NOW(),1)

__________________
-Carl

06-12-2006, 01:04 PM

Peter_Aquino Peter_Aquino is offline

Contributor

* Expert *

Join Date: Sep 2004

Location: San Diego, CA, USA

Posts: 732

You could also use:

Code:

=TODAY()+INDEX({6,5,4,3,2,1,0},WEEKDAY(TODAY(),1))

06-13-2006, 12:23 PM

blindwig

Ultimate Contributor

* Expert *

Join Date: Jun 2003

Location: New York, NY

Posts: 1,929

The WEEKDAY() function - that's just what I was looking for!
I needed a function like that to translate the day into a numerical indew for the week, and I did get the LOOKUP() function working, but of course that required using a table on a hidden sheet.

Thank you both. Both solutions worked equally well. I used Wamphyri's because it was a little shorter.

FYI, my original solution was this:
=NOW()+LOOKUP(TEXT(NOW(),"dddd"),LookUps!$A$2:$A$8,LookUps!$B$2:$B$8)
With A2-A8 being Sunday-Saturday and B2-B8 being 6-0

Thanks again!