6 Months Ahead If Falls On Monday If Not Next Monday Date
Sep 23, 2007
I am currently using this formula =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) to get me 6 months ahead from a certain date. There is a new problem that has arose for me. I need a formula that will give me 6 months from a certain date if it falls on a Monday if the 6 months does not fall on a Monday then give me the next Monday date.
for example 6 months from 1/2/2007 is 7/2/2007 which falls on a Monday which is fine but, If the start date was 1/3/2007 this falls on a Tuesday so I would need the next Monday date 7/9/2007.
View 9 Replies
ADVERTISEMENT
Jul 29, 2008
The clue's in the title. I have a date in cell (BC25). I need to jump forward by one year then if the date doesn't fall on a Monday I need to find the next Monday.
I've looked at this thread which should point the way but I'm particularly dim this morning and can't get it to work.
http://www.mrexcel.com/forum/showthr...ghlight=monday
View 9 Replies
View Related
Jan 16, 2007
Is it possible to to return a date that begins on following mondayand adds days unless it was entered on a monday then just add 17 days
if someone enters a date say 10/01/07 (wednesday) i need to be able get the following mondays date and add 10 days to it IE 25/01/07
View 14 Replies
View Related
Apr 24, 2006
to determine the number of 1st Mondays or any weeday e.g. 5th Fridays between a date range e.g. 1/1/2006 - 4/1/2006.
I've noticed a lot of threads for calculating just one month but none for a period of time.
View 14 Replies
View Related
Feb 2, 2004
This formula on the forum has helped me to find the first day I specify of the month of a certain year. (here a monday in august of the year specified in B1)
=DATE(B1;8;8-WEEKDAY(DATE(B1;8;1);3)-IF(WEEKDAY(DATE(B1;8;1);3)=0;7;0))
As I'm not really good at understanding the formula (I get confused with the =0;7;0 bit), even though I can make it work I'd like to know how I can find the a last specific day (mo-su) in a month.
View 9 Replies
View Related
Sep 20, 2006
I want to be able to test if a date entered in a cell (Format dd/mm/yyyy) equals a "Monday". If it doesn't, I want to display a warning message.
View 6 Replies
View Related
Nov 23, 2009
I'm trying to find the calculation which will automatically have the date for the first Monday in January to show
e.g. Cell A1 input 2010 Cell A2 would show 04/01/2010
Cell A1 input 2011 Cell A2 would show 03/01/2011
etc
View 3 Replies
View Related
Mar 17, 2013
The portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.
View 3 Replies
View Related
Aug 24, 2014
I have a list of dates and I need a formula that will return the date of the following Monday, IF the date falls on a Friday, Saturday or Sunday.
View 3 Replies
View Related
Apr 16, 2014
I have a formula
=AL260-WEEKDAY(AL260,2)+MATCH(AP260,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)
The formula looks at the chosen start date (AL260), and then uses a displayed text day (AP20) to display the relvent date to the text day, this works fine however if the start date is say 08/08/14 (a Friday) when the formula detects a Monday (this copies down a range looking at the start date and the text days to produce dates on each line, the formula displays the Monday dates prior to start date, is there any way to alter this to make it display the dates ahead of the chosen start date?
View 10 Replies
View Related
Jun 23, 2006
I am writing a module which have a different test based on for each of the previous 2 column cells. It calculate the days passed or in simple way calculate the date difference for the 2 columns and puts them in third. But i need to use a inputbox for entering the first date for monday of the month each time i use the worksheet.
When i try using the module for each cell of the column it display the input box for each cell. is there any way so that i just enter the value in input box and it can be used in rest of the module.Without using it again and again.
View 8 Replies
View Related
May 4, 2009
I need a VBA code which tells me the date of previous monday of any date. The user types in any date to the cell A1, and I need the date of previous monday to be inserted to the cell A2.
For example:
A1: 9/5/2009 --> A2: 4/5/2009
A1: 19/5/2009 --> A2: 18/5/2009
A1: 27/12/2009 --> A2: 21/12/2009
View 4 Replies
View Related
Oct 4, 2012
Is there a formula that would tell me the last Monday of any given year ?
View 5 Replies
View Related
Jan 26, 2014
Outlook allows you to easily specify, but eExcel always treats Sunday as the first day of the week by default. Yes you can manipulate that within formulas and functions, but if you use the autofilter in excel and choose "this week" (or "next week", or "last week") excel considers Sunday as the first day of the week. I would like to know how (or if) this can be changed to Monday as the first day of the week.
View 5 Replies
View Related
Dec 25, 2006
I am currently looking for a formula that will give me the actual date for the first Monday of the week.
I have for example in column A dates from 1st Jan 06 to 31st Jan 06 I just need to workout what the date is for the first Monday then after that for the 2nd Monday it would just be the 1st Monday +7.
View 9 Replies
View Related
Apr 9, 2014
I need a macro that allows a user to input a Month and a Year on an input box. When this is done the macro would pull out the date for each Monday (in the format DD-MM-YY) in that month and paste it into four or five cells (A1:A5)
Eg for April 2014
A1
A2
A3
A4
A5
07-04-14
14-04-14
21-04-14
28-04-14
View 2 Replies
View Related
Apr 20, 2009
In cell AI12, i want it to show the value in cell AE12, however, if AE12 is empty i want it to show the value from cell AD12, if AD12 is empty i want it to show the value from AC12 but is AC12 is empty i want it to remain empty.
View 9 Replies
View Related
Feb 16, 2007
I have seen this before and once figured it out, but I have not had any luck this time. I'm looking for a formula that will reference a cell that contains a date and will then return the date of the Monday of the given date's week. If I gave it a cell that contained 2/15/07 it would return 2/12/07. Probably a simple question but I think I must not be working on all cylinders. =)
View 6 Replies
View Related
Apr 30, 2009
I am trying to write a function that will display the first Monday of the year based on the user entry.
For example, if I enter;
5/1/2011, It should produce 1/3/2011...
12/31/2012, it should produce 1/2/2012..
View 4 Replies
View Related
Jul 30, 2014
The required results from the below data are:
Required Result
Week 25 = Batches 25
Week 26 = Batches 40
Data
Data - Day
No. of Batches
16/06/14 Mon
5
17/06/14 Tue
6
18/06/14 Wed
9
[Code] ..........
View 9 Replies
View Related
Jan 17, 2008
In cell A1 I have a date (like 8/1/08)
In A3, B3, and C3 I need to have the first Monday, Thursday, and Friday of the month. Here's the caveat, I need A3 to have the day that comes earliest in the month.
In this case, A3 would contain 8/1/08 since it's the first Friday, B3 would have 8/4/08 as the first Monday, then C3 would have 8/7/08 as the first Thrusday.
If A1 contained 7/1/08 then A3 would have 7/3/07 as the first Thursday with Friday and Monday following.
View 9 Replies
View Related
Aug 17, 2008
What I was told to do was to create an Excel Macro that would record 7 fields via pop-up box. I had no problem with this (see code below) but now I would like to run that same macro for 10 or more students. I would like to have the same data collected (first name, last, email, address, phone, etc) for each student, but have excel compile a list of the recorded data for each individual. Ideally John, Mary, Will, Greg, and Matt would fill out the form and their responses would appear as:
First Last Email Phone Other
John Doe johndoe@yahoo.com 5551245 etc etc
Mary Jones mjones@gmail.com 5551227 etc etc
Next Student student@google.com 5551212 etc etc
The macro is started by clicking on a button to register for a free giveaway
Originally Posted by code
Sub RetisterToWin()
Dim FirstName As String
Dim LastName As String
Dim Email As String
Dim MailingAddress As String
Dim PhoneNumber As String
Dim Major As String.............
View 9 Replies
View Related
Oct 3, 2011
Is there a way to create a dynamic chart to automatically run from the most recent Monday to Sunday. So on Monday the entire chart will reset?
View 9 Replies
View Related
Dec 4, 2011
I have a table and I want to find out the what is max number for Apple sold on Monday.
FruitsQtyDaysApple20SaturdayApple50SundayApple100MondayApple500MondayApple20
MondayMango50MondayBanana640FridayMango30FridayGet MAX ForApples on Monday500
View 9 Replies
View Related
Jun 21, 2014
In column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.
18/06/14
18/06/14
19/06/14
19/06/14
19/06/14
21/06/14
23/06/14
21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.
Is there a method with VBA (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.
I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future.
View 9 Replies
View Related
Jul 16, 2014
Another thread that wanted to count "weekends" got me to thinking about this one:
Count the full weeks (from Monday thru Sunday) between 2 dates.
For example...
Data Range
A
B
C
D
E
F
1
Date
Weekday
------
Start
End
Weeks
2
8/1/2013
Thu
8/1/2013
8/15/2013
1
3
8/2/2013
Fri
[Code] .........
Full Monday thru Sunday weeks = 1
This array formula** entered in F2:
=(MAX(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=7,ROW(INDIRECT(D2&":"&E2))))-MIN(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=1,ROW(INDIRECT(D2&":"&E2))))+1)/7
** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key then hit ENTER.
The logic of the formula is:
The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks
View 14 Replies
View Related
Jan 22, 2010
Is there any weekly leave planner that shows the dates of the mondays in the month? eg in Jan we have 4,11,18,2
View 3 Replies
View Related
Feb 8, 2012
Can weeknum work with the week starting on saturday instead of sunday or monday?
if not, what are my options?
View 4 Replies
View Related
May 20, 2013
Macro to highlight a cell A1 every monday ( or any day) when we open the book for the first time.
Macro should not run when we open the book from the next time till that week gets over and again it should run once the next week started.
View 1 Replies
View Related
Aug 14, 2012
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
View 1 Replies
View Related
