What would the formula look like for finding Monday's date when it's not Monday? For example if the day were Thursday, January 18th 2007 the answer would be 1/15/2007. There has to be a simpler answer than having a bunch of nested if statements....right? Any help would be great.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Finding Monday's date 3
- Thread starter mrdod
- Start date
- Thread starter
- #2
-
1
- #3
CBasicAsslember
Programmer
For the preceding Monday:
=ROUND((TEXT(A1,"?"))/7,0)*7-5
For the following Monday
=ROUND((TEXT(A1,"?"))/7,0)*7+2
Make sure you format the cell for dates.
=ROUND((TEXT(A1,"?"))/7,0)*7-5
For the following Monday
=ROUND((TEXT(A1,"?"))/7,0)*7+2
Make sure you format the cell for dates.
- Thread starter
- #4
-
2
- #5
CBasicAsslember
Programmer
We have a winner! Have a star macropod.
- Thread starter
- #7
I know you did posted this a long time ago and I'd like to get a better understanding of how it works plz. I'm not really familiar with the MOD function but understand that it returns the remainder or something like that? Since MOD is being used is the date in A1 automatically a value? Such a simple yet powerful formula I am just really curious how it works. So if you could break it down a little bit I would really appreciate it.
Hi mrdod,
The MOD function returns the remainder after divising one number by another. Thus, 13 MOD 10 (or MOD(13,10) in Excel parlance) is 3. If the number divides exactly, the remainder is 0.
All Date values in Excel are just numbers which, when divided by 7 leave a remainder (0=Saturday to 6=Friday). Thus MOD(Date,7) returns the day of the week, with 0 being Saturday. If I deduct the raw MOD value from today's date, I'll get the most recent Saturday.
What I wanted to do in this case, was to find the most recent Monday. So, I needed to make Monday return 0 from the MOD function. Since the MOD value for Monday is 2, MOD(Date-2,7) returns 0 for Monday.
It's then just a simple matter to deduct the MOD result from the date to return the most recent Monday.
Cheers
[MS MVP - Word]
The MOD function returns the remainder after divising one number by another. Thus, 13 MOD 10 (or MOD(13,10) in Excel parlance) is 3. If the number divides exactly, the remainder is 0.
All Date values in Excel are just numbers which, when divided by 7 leave a remainder (0=Saturday to 6=Friday). Thus MOD(Date,7) returns the day of the week, with 0 being Saturday. If I deduct the raw MOD value from today's date, I'll get the most recent Saturday.
What I wanted to do in this case, was to find the most recent Monday. So, I needed to make Monday return 0 from the MOD function. Since the MOD value for Monday is 2, MOD(Date-2,7) returns 0 for Monday.
It's then just a simple matter to deduct the MOD result from the date to return the most recent Monday.
Cheers
[MS MVP - Word]
Hi macropod,
that's a good explanation.
Although I don't think that it's obvious to many people that MOD(Date,7) returns day of the week ( just luck of the draw that the serial start date of 1/1/1900 happened to be a Sunday ). I usually recommend using WEEKDAY to get day number of week, and there are 3 options:
giving a result equal to the previous Monday.
Cheers, Glenn.
Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
that's a good explanation.
Although I don't think that it's obvious to many people that MOD(Date,7) returns day of the week ( just luck of the draw that the serial start date of 1/1/1900 happened to be a Sunday ). I usually recommend using WEEKDAY to get day number of week, and there are 3 options:
As you can see, a return_type of 3 gives the return we can use in the required formula:
Code:
=A1-WEEKDAY(A1,3)Cheers, Glenn.
Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
AnotherHiggins
Technical User
For what it's worth, I always use this:
=INT((A1[red]-2[/red])/7)*7[blue]+2[/blue]
-[red]This part determines on what day the returned date changes. The default is to switch on Saturdays.[/red]
-[blue]This part determines what day of the week is returned.[/blue]
CBA: You would need to change TEXT(A1,"?") to TEXT(A1[highlight]+[/highlight]2,"?") to make the formula change over on Mondays instead of Wednesdays.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ181-2886 before posting.
=INT((A1[red]-2[/red])/7)*7[blue]+2[/blue]
-[red]This part determines on what day the returned date changes. The default is to switch on Saturdays.[/red]
-[blue]This part determines what day of the week is returned.[/blue]
CBA: You would need to change TEXT(A1,"?") to TEXT(A1[highlight]+[/highlight]2,"?") to make the formula change over on Mondays instead of Wednesdays.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ181-2886 before posting.
Hi Glen,
Cheers
[MS MVP - Word]
No it wasn't - it was a Monday! The reason Excel reports it as a Sunday is because Lotus mis-programmed 123 and and treated 1900 as a leap year, which it wasn't, and MS programmed Excel to behave the same way for compatability reasons. Thus, Excel is off by one day for all dates before 1 March 1900.
Cheers
[MS MVP - Word]
- Status
Similar threads
- Locked
- Question