First Monday, Thursday, And Friday Of Month
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return Last Friday From A Given Month
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 Replies!
View Related
1st Monday In The Month
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 Replies!
View Related
Formula For Date Of Last Monday Of The Month
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 Replies!
View Related
First Code In VBA: Use A Inputbox For Entering The First Date For Monday Of The Month Each Time I Use The Worksheet
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 Replies!
View Related
6 Months Ahead If Falls On Monday If Not Next Monday Date
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 Replies!
View Related
Formula To Find Next Thursday's Date
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 Replies!
View Related
Reminder On Every Other Friday
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 Replies!
View Related
Calculating Friday Date
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 Replies!
View Related
Generate A List With The Date Of Every Other Friday
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 Replies!
View Related
Formula Return Following Wednesday Or Friday Date
Need a formula for cell J27 that returns the date of the following Wednesday from a date in cell J2 that's a Wed, Thurs, or Fri or return the date of the following Friday for a date in cell J2 that's Sat, Sun, Mon, or Tue. For example, when 05-29-08, a Thursday, is entered in cell F2, I need cell J27 to return the following Wednesday's date of 6-04-08. or For example, when 06-01-08, a Sunday, is entered in cell F2, I need cell J27 to return the following Friday's date of 06-06-08.
View Replies!
View Related
Monday Morning IF Blues
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 Replies!
View Related
Get First Monday After Set Date
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 Replies!
View Related
Return Monday Of Given Week
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 Replies!
View Related
Popup Macro, New To Vba, Due Monday Night
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 Replies!
View Related
Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)
I have log data in two columns: Column A: Date/time (at 30 minute intervals) Column B: Numeric data On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C. The end of the range is determined by the month in the current row. I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month. I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach. Manual =IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them. =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View Replies!
View Related
Automatically Bold And Highlight The Current Month’s Total And Month Name
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name. Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month
function in a spreadsheet that will list all of the days in a given month automaticaly with the entry of the 1st of the month only. Ex; 10/01/05 entered dated 10/02/05 auto fill 10/03/05 " . " . " 10/31/05 end of auto fill I would like the function to stop filling dates at end of the month even for shorted months such as Feb.
View Replies!
View Related
Auto Format Spreadsheet With Various Rows Month To Month
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month. Goal: I need to format the spreadsheet to make it more readable and have been assigned the task of: 1 - Inserting a blank row between each row that contains data and filling in with color. 2 - Resizing the blank row to make it look like a "thick" border. 3 - Auto adjusting the columns to correct size. 4 - The last column contains comments and needs to be wrapped text. 5 - All of this needs to fit on 1 sheet (landscape). Issues: 1 - Each month there will be a different number of rows. 2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls? I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.
View Replies!
View Related
Adding Or Subtracting One Month To A Month Number
I have forumlas that will look at this cell and take action of the month in a different cell is either 1 month greater (Frontmonth+1) or less (Frontmonth-1) than "Frontmonth". As we approach December I'm realizing that logic will breadown since the FrontMonth+1 would be 13, not 1 (January) Is there a way to get excel to add 1 month to just the month number so that if Frontmonth = 12, Frontmonth+1 would return 1, not 13?
View Replies!
View Related
Results By Month And Week Of Month
I have a range of data which is as follows: Week in month: 1 1 1 5 Site: 01/03 02/03 03/03 etc 30/03 etc Leeds 10 9 15 20 Manchester 8 5 1 2 Etc Here's what I need to produce: March 08 April 08 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Leeds Manchester I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.
View Replies!
View Related
Last Day Of Month Reverts To First Of Month
I'm doing a web query that brings down stock dates/prices. The dates come down as mm/yyyy. I then format the column as mm/dd/yy which changes the date to mm/01/yy. Then I do a loop that converts the dates to the date of the last weekday of the month. I see the changed dates written back to their cells, but by the time I leave the called conversion subroutine and hit the break on the next line of code, the dates have all reverted back to the first of the month. In a case like this, I use the Step mode in VBE to step through the code while I have the worksheet the code is acting on visible. That way you can actually see which line in the code converts the value back to the original value or if indeed it does put the eomonth date in the active cell. By the way, I'd suggest you use the equivalent of the eomonth worksheet function directly in the macro rather than pasting the date to another cell and reading the eomonth from another cell containing the formula for eomonth.
View Replies!
View Related
Add EXACTLY One Month To Another Month
I would like to enter a month into a cell (B5) and have each cell below it automatically calculate one month from that date. Here is what kind of result I am looking for: 5/5/2006 -- User Enters Start Date Here. 6/5/2006 -- Rest of dates populated exactly 1 month from prior date 7/5/2006 8/5/2006 9/5/2006 10/5/2006 11/5/2006 12/5/2006 1/5/2007 2/5/2007 3/5/2007 4/5/2007 5/5/2007 6/5/2007 7/5/2007 8/5/2007 9/5/2007
View Replies!
View Related
Month And Weekdays Of That Month
I have given up after 2 hours of trying, so here I am again. I would like the current month to automatically appear in cell B4, just like using =TODAY() BUT, once the sheet has had data entered into it for that month, the month (B4) cannot then change next month when the spreadsheet opens. Once the month has appeared in B4, I would then like the weekdays of that month to appear in B7:B30.
View Replies!
View Related
|
TRACKER
