PDA

View Full Version : [SOLVED] Formula to return monday of current week



Sir Babydum GBE
08-02-2005, 06:29 AM
Hello, my subject kind of said it all: What is the formula that will return the monday of the current week?

Your help is greatly appreciated!

Sir Babydum GBE
08-02-2005, 06:45 AM
Ok, I kinda worked this out myself with a long nested IF.
=IF(WEEKDAY(TODAY())=7,TODAY()-5,IF(WEEKDAY(TODAY())=6,TODAY()-4,
IF(WEEKDAY(TODAY())=5,TODAY()-3,IF(WEEKDAY(TODAY())=4,TODAY()-2,IF(WEEKDAY(TODAY())=3,TODAY()-1,
IF(WEEKDAY(TODAY())=2,TODAY(),TODAY()-7))))))

Out of interest - anyone know of an easier way?

Bob Phillips
08-02-2005, 07:01 AM
Slightly!


=TODAY()-CHOOSE(WEEKDAY(TODAY()),6,0,1,2,3,4,5)

That version takes a Sunday date to the previous Monday (i.e. week starts on a Monday). If you want the next Monday (ie week starts on a Sunday), use


=TODAY()-CHOOSE(WEEKDAY(TODAY()),-1,0,1,2,3,4,5)

Is that a new Avatar?

.

Sir Babydum GBE
08-02-2005, 07:32 AM
That's great xld!

But unfortunately my spreadsheet is more complicated than that - and I can't use the returned date in the way I wanted to - so I'm about to post a new thread with an attachment and more detailed explanation.

Still need the date returned - so sill very useful.

My avatar? New? Well it's been around for a good few weeks now. Only just noticed it?

Bob Phillips
08-02-2005, 09:26 AM
My avatar? New? Well it's been around for a good few weeks now. Only just noticed it?

Yeah, I try to ignore your posts, but I failed miserably.

Sir Babydum GBE
08-02-2005, 01:25 PM
Yeah, I try to ignore your posts, but I failed miserably.

How rude! Perhaps you should post a question in my "Solutions of the week" thread along the lines of "How can I successfully ignore Babydum's posts?" I'm sure my experienced staff and I will be able to provide a suitable answer to your dilemma!

How goes it anyway, you miserable tike?

Bob Phillips
08-02-2005, 01:46 PM
How rude! Perhaps you should post a question in my "Solutions of the week" thread along the lines of "How can I successfully ignore Babydum's posts?" I'm sure my experienced staff and I will be able to provide a suitable answer to your dilemma!

How goes it anyway, you miserable tike?

Miserable. How about you? Bit busy at the mo, got a deadline to meet, and the weather is so nice - sigh!

And then I sit awaiting your new thread because my last answer wasn't good enough.

BTW, I'm not a tyke, you're from those parts, no?

BDavidson
08-02-2005, 01:52 PM
To further cloud the issue :)

=1-WEEKDAY(TODAY(),2)+TODAY()

Sir Babydum GBE
08-02-2005, 01:58 PM
Miserable. How about you? Bit busy at the mo, got a deadline to meet, and the weather is so nice - sigh!

And then I sit awaiting your new thread because my last answer wasn't good enough.

BTW, I'm not a tyke, you're from those parts, no?Ha ha! Your answer was fine! It was my assumption that I would be able to use the result of the formula in a whole host of other calculations that was duff. I decided not to post another thread immediately in the end, because I thought it would be lazy to do so. So I'm having a good look at the spreadsheet to see if I can fix it. I have a guru-like reputation to live up to now - so one has to try before he asks you for more help!

Deadlines... I love deadlines. I like the whooshing sound they make as they fly by.―Stolen

Sir Babydum GBE
08-02-2005, 02:13 PM
To further cloud the issue :)
=1-WEEKDAY(TODAY(),2)+TODAY()

Well hello Mr Davidson! (I assume it is Mr Davidson and not Mr BDavidson!) I must reluctantly admit that your formula does look a tad shorter than XLD's ("reluctantly" because I understand that XLD has a mean right-hook and is a master at the pincer movement).

However, the advantage to the first solution (not including my war-and-peace effort) is that it has taught me that there is such a function as CHOOSE. (I mean that, Mr X).

But thanks, because it is definitely shorter - though size isn't everything, ya know!

BDavidson
08-02-2005, 02:48 PM
...I assume it is Mr Davidson ...
Correct, heavy on the Mr :whistle:


...I understand that XLD has a mean right-hook and is a master at the pincer movement...
Here's hoping he doesn't look me up : pray2:


...though size isn't everything, ya know!
My motto in life :rofl: (I'm sure getting tired of the emails offering to increase the size of my p*n*s).

Bob Phillips
08-02-2005, 03:14 PM
Correct, heavy on the Mr :whistle:

Yours may be shorter if you assume Monday as the start of the week, but if you assume Sunday, mine just needs a differentr value, whereas yours (AFAICS) requires an extra nested function


=TODAY()+1-WEEKDAY(TODAY(),2)+(WEEKDAY(TODAY())=1)*7

Wessex rules!


Here's hoping he doesn't look me up

It's okay, it is only recalcitrant tykes that arouse my wrath.


My motto in life :rofl: (I'm sure getting tired of the emails offering to increase the size of my p*n*s).

I get worse than that, 50 or so a day.

Sir Babydum GBE
08-02-2005, 03:33 PM
Yours may be shorter if you assume Monday as the start of the week, but if you assume Sunday, mine just needs a differentr value, whereas yours (AFAICS) requires an extra nested function Yeah Mr BD! Take that! (Excuse me Mr X - but that's not how you spell AFRICA you know! - There's no "S")


=TODAY()+1-WEEKDAY(TODAY(),2)+(WEEKDAY(TODAY())=1)*7 See! And if you wanted it to return the current day - you'd need a whole 32 other nests! So there!


Wessex rules! Whatever Mr X, everyone know that Yorkshire bakes the best metaphoric cake!


It's okay, it is only recalcitrant tykes that arouse my wrath. Oi - not so much of the recalcitrant please!

I take it all back: Mr BD - you win!

BDavidson
08-03-2005, 09:13 AM
I get worse than that, 50 or so a day.
Kinda makes you wonder who's been talking, hmmmm :think:


Mr BD - you win!
Yay!!! Finally I'm a winner at something :*) :*)