Calculate Last Second of Last Year?

  • Thread starter Thread starter Patrick A
  • Start date Start date
P

Patrick A

All,

I'm trying to filter a query, and I only want this year's results, and
I don't want to have to remember to change the cell containing the
date parameter when I'm all sleepy on Jan 1.

Is there a formula I can string together that will return the date/
time value for the last second of last year?

In other words, today it would return 12/31/2009 23:59 (or some
numeric equivalent).

Next year it would return 12/31/2010 23:59 (or some numeric
equivalent).

(I am not so much concerned with the format as I am with the "number"
behind it.)

Thanks
 
Patrick A said:
Is there a formula I can string together that will return
the date/time value for the last second of last year?
Click to expand...

=date(year(today())-1,12,31)+time(23,59,59)


----- original message -----
 
Bob, Joe,

These work great.

What I didn't realize is that I will also want to "detect"
the date of the first day of the current month and
the date of the first day of the current year
Click to expand...

I've tried various adjustments to the formulas you sent me, but I keep
moving back a day at a time, etc.

Any suggestions?

Thanks again.
 
Patrick A said:
What I didn't realize is that I will also want to "detect"
DATE(YEAR(TODAY()),MONTH(TODAY()),1)
Click to expand...

DATE(YEAR(TODAY()),1,1)


----- original message -----
 
So I think figured rhese out;

Last date of last month:
=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

Last date of last week:
=TODAY()-6-WEEKDAY(TODAY())+(1<WEEKDAY(TODAY()))*7

They give me the correct answer today - are these reliable?

Thanks again,

Patrick
 
Patrick A said:
So I think figured rhese out;
Last date of last month:
=DATE(YEAR(TODAY()),MONTH(TODAY()),0)
Click to expand...

Well, you had previously asked for "date of the first day of the current
month". But yes, your formula works to the last date of the previous month.
Simpler:

=today() - day(today())

For that matter, the first day of the current month (previous request) can
be:

=today() - day(today()) + 1

Last date of last week:
=TODAY()-6-WEEKDAY(TODAY())+(1<WEEKDAY(TODAY()))*7
Click to expand...

You need to specify what day of the week is the first day. If it is Sunday,
the last date of the previous week is Saturday. This is given by:

=today() - weekday(today())


----- original message -----
 
Thanks Joe,

Perhaps you can help me simplify these:

Last Day of the Current Month: =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

Last Day of the Current Week: =TODAY()+1-WEEKDAY(TODAY())
+(1<WEEKDAY(TODAY()))*7

?

Patrick
 
Patrick A said:
Perhaps you can help me simplify these:
Last Day of the Current Month:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
Click to expand...

That's what I would do if you don't want to use EOMONTH(TODAY(),0).

Last Day of the Current Week:
=TODAY()+1-WEEKDAY(TODAY())+(1<WEEKDAY(TODAY()))*7
Click to expand...

I think that's simply:

=today()-weekday(today())+7
 
Back
Top