First Monday, Thursday, And Friday Of Month
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
ADVERTISEMENT
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
Jun 18, 2014
I am mailing a letter on the 15th day and the 45th day after a hire date. The effective date will be the 1st of the month following 60 days. A meeting will be held every 3rd Thursday of the month for all new hires. What I am trying to accomplish is calculating the 3rd Thursday of the month PRIOR to the 1st of every month.
i.e. hire date is 3/11/14 - 1st letter goes out 3/26/14 (15 days) - 2nd letter goes out 4/25/14 (45 days).
Their eligibility date is 6/1/14 (1st of the month following 60 days).
The meeting is held on 5/15/14 (3rd Thursday of the month prior to the first of the month (June))...
HIRE DATE LIST.xls
View 1 Replies
View Related
Mar 25, 2012
If there's one thing I really wish I understood more in Excel its how to use formulas to calculate days and times.
I'm looking for a formula that will give me the date of the last Friday in a month based on another date. I have the formula below at the moment that works quite nicely for March 2012 but not for any others. It's getting the 5 at the end to be more flexible that's got me stumped.
=DATE( YEAR(H12), MONTH(H12) + 1, 0) - (WEEKDAY( DATE( YEAR(H12), MONTH(H12) + 1, 0), 2) - 5 )
View 2 Replies
View Related
May 23, 2007
Re: Return last Friday from a given month
This will calculate the last Friday of the month in which the date in A1 falls:
=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)
HTH
TJ
actually this thread is closed. I want to convert this into UDF but not able.
View 8 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
Sep 14, 2008
Without VB, is there a way to write a formula which when given a certain month, will output the date of, let's say, 2nd or last friday of the month?
View 14 Replies
View Related
Nov 11, 2009
I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?
View 3 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
Oct 18, 2013
I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:
Code:
=DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),4)),2,1,7,6,5,4,3))
The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:
[URL]
View 4 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
May 8, 2014
How to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.
For example...
today is may 8th
So if I run the code the dates returned for last friday should be 05/02/2014
and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014
View 6 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
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
View Related
Mar 11, 2014
I am trying to write a macro to send mail on every Friday and also on last day of every month. If the last day of the month falls on Saturday or Sunday then the macro should mail on Friday. I have written a separate macro to send a mail. I have also written to check day(ie Monday, Tuesday etc) of today. If today is Friday or month end i can send mail. I dont know how to tell the macro to send mail on friday if the month last date is saturday or sunday.
Sub done()
Dim Dat As Date, x As Integer, y As Date, sorry As String
Dim str As String
sorry = "Today is not friday or month end. So i cannot send mails"
str = WeekdayName(Weekday(Now()))
[Code] ........
View 1 Replies
View Related
May 28, 2009
Subtracting 1 day when the weekday is a Thursday. I need with the formula above (on AA2):
View 2 Replies
View Related
Jun 27, 2014
I am trying to use configure a code similar to this...
=IF(TEXT(A1-1,"dddd")="Friday",A1-1,IF(TEXT(A1-2,"dddd")="Friday",A1-2,
IF(TEXT(A1-3,"dddd")="Friday",A1-3,IF(TEXT(A1-4,"dddd")="Friday",A1-4,
IF(TEXT(A1-5,"dddd")="Friday",A1-5,IF(TEXT(A1-6,"dddd")="Friday",A1-6))))))
But to identify the date closest that is not a Monday, Tuesday or Friday.
For example... The attached sheet cell C33.
View 2 Replies
View Related
Apr 14, 2009
I need a formula to find the delivery date for an order. If the order is placed by Friday, it will be delivered on the following Thursday as long as it isn't a holiday when it would be the following Friday. I have the holidays listed in a range of cells I2:I5.
OrderedDelivery4/3/20094/9/20094/6/20094/16/20094/7/20094/16/20094/8/20094/16/20094/9/20094/16/20094/10/20094/16/200911/20/200911/27/2009
Is it possible? If I can calculate the next Thursday part without taking into account the holidays,
View 9 Replies
View Related
Feb 9, 2014
I have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
View 6 Replies
View Related
Jun 12, 2014
I have two columns with total hours worked at different places and a grand total for both together in a third column.
I'm trying to sum the total hours for the week so far from Thursday till the following Wednesday for each week.
I want the wookbook to look up todays date and tell me what the total hours are for the current week
I'm using Excel 2003 - see the file attached
View 14 Replies
View Related
Dec 20, 2007
Every other Friday, I need to submit my biweekly work hour sheet to manager. For example, as 12/21/2007 is Friday and pay day, I need to submit my sheet. Next time is two weeks later, 1/4/08, and on.
12/21/07 is the first event.
I want to create a conditional format, or workbook events, or worksheet events to remind me on that Friday when I open that book or activate that sheet.
View 9 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Feb 12, 2008
how to write an excel workbook function that will return last friday's date?
For example: If Today is 2/12/2008, it will return 2/8/2008 as the date
View 6 Replies
View Related
Feb 28, 2005
how can i change nonworking date from saturday&sunday to frinday&Saturday
View 11 Replies
View Related
Apr 7, 2009
I have a column that has dates going vertically down from 1/1/2009 - 1/7/2010. I need a formula that will return the date for every other Friday beginning with the second Friday in January 2009.
View 6 Replies
View Related
Aug 7, 2006
I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code that determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large.
View 9 Replies
View Related
May 30, 2009
On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:
Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"........................
What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).
View 10 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
