Calculate Last Second of Last Year?

[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

 

[Bob Phillips]

Try

=DATE(YEAR(TODAY()),1,1)-1/24/60/60

 

[Joe User]

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

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


----- original message -----

 

[Patrick A]

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

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.

 

[Joe User]

What I didn't realize is that I will also want to "detect"
DATE(YEAR(TODAY()),MONTH(TODAY()),1)

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


----- original message -----

 

[Patrick A]

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

 

[Joe User]

So I think figured rhese out;
Last date of last month:
=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

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

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 -----

 

[Patrick A]

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

 

[Joe User]

Perhaps you can help me simplify these:
Last Day of the Current Month:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

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

I think that's simply:

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

 

[Patrick A]

Thanks Joe

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


I think that's simply:

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