Calculating Friday Date
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
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
Feb 17, 2014
If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.
View 3 Replies
View Related
Feb 14, 2013
I need to work out how to get the last (or most recent) Friday date. Suppose today is 14/02/2013, then the previous date is: 08/02/2013. If today is 15/02/2013, then I just want it to be 15/02/2013 as it is Friday.
View 3 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
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
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
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
Jun 10, 2008
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 9 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
Mar 18, 2009
is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?
Dates will appear in a column like this:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar
View 4 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
Mar 14, 2014
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
View 9 Replies
View Related
Sep 16, 2009
how to calculate the age from the date of birth and was given these two responses
1) I may be way off here but all that coding seems a waste !
I have in cell A1 = BLANK <--- enter your DOB here
I have in cell A2 =NOW()
in cell A3 do = A2-A1
Format that to General
In Cell A4 do =A3/365
Format that to no deciamal places and you get the age - this one is adding a year to everyones age?
2) Alternatively, if the DOB is A1 :-
=INT(YEARFRAC(A1,TODAY(),1)) - This one does not work it is saying there is an error with the "yearfrac"?
It is also when I am trying to put these into a pivot table, the pivot table is listing these down to 5 decimal places( when I have formatted to zero)????
View 2 Replies
View Related
Mar 25, 2014
At the moment I have a simple formula that calculates the due date of a task
The log in date is in cell B3, I use a simple formula in cell F3 "=B3+20" - This gives me the due date
I now need to add another log in date but use the same due date cell
So now ....
Either B3 or D3 will contain a date - whichever one is populated I will need the due date displayed in cell F3
View 10 Replies
View Related
Sep 6, 2005
http://www.cpearson.com/excel/datedif.htm
--
"malvis" wrote:
> Is there a formula that can calculate age when given only Date of Birth?
View 14 Replies
View Related
Oct 13, 2011
I have a need to calculate the latest and earliest date in a given range, but I cannot seem to make it work properly. The range looks like this:
Code:
23/08/201120/08/2011 13/08/11 13/08/11 13/08/11 13/08/11
and the code is:
Code:
d1ST = Application.WorksheetFunction.Max(rRow)
No matter the information in the range, the statement is returning "0" (or "12:00:00") - I have tried setting d1ST as a date and double data type.
View 1 Replies
View Related
Nov 24, 2012
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
View 2 Replies
View Related
May 2, 2014
I have a list of several hundred loan recipients who are all starting to pay their loans back on different dates. I am able to get the calculation to work so that I just need to put in the start date and excel works out the other 5 or 6 payment dates.The problem is, some people pay back on the 29th, 30th of the month which is giving me a headache when they are supposed to pay in February. The formula I am using calculates the next date of payment to be on the 3rd March (skipping the February payment all together).
The formula I am using is : =DATE(YEAR(A10),MONTH(A10)+1,DAY(A10))
View 3 Replies
View Related
Apr 24, 2007
I am trying to create a formula that returns the date on the friday (ie last working day) of whatever week I am currently in. So, for example, I guess I would use the 'TODAY' or 'NOW' function somewhere to determine the present date - but how do I then determine the date on the Friday of the current week?
View 9 Replies
View Related
Oct 4, 2009
I was trying to figure out how you could caluculate a project end date, based on a start date (dd,mmm,yyyy) and a project duration of lets say 5 work day and the work week is five days long (monday to friday). If i have a leave on the week days like tuesday is bank holiday then it should give be the date excluding that tuesday and wednesday leave and caluculate 5 working days and shoule give the project end date.
Example date: 5-Oct-09
Project end date:09-Oct-09
If i have bank leave on 06-Oct-09 means.
My project end date:12-Oct-09
Note:Bank holidays are fixed date example(every year bank holiday 06-oct-09,02-Dec-09,25-Dec-09)
Please dont need a macro.
View 9 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
Apr 11, 2014
I am trying to determine a formula that will tell me what the date will be 61 days and 90 days from a selected date. For example:
A1: 04/11/2014
A2: (Date 61 days from date in A1)
A3: (Date 90 days from date in A1)
Is this possible??
View 6 Replies
View Related
Feb 15, 2014
I have a contract start date. The payment is due every seven days. I have entered the start date. i need the "next due date" to automatically calculate in the "due date column". here is my problem if a customer has a due date say on 2/15/2014 his next due date should be 2/22/2014. regardless if he pays on the 14th or 21st. i need this date to change each time a payment is made.
View 7 Replies
View Related
Jun 12, 2009
I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week.
What would be the code to....
In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell.
Does anyone have any ideas how to do that?
View 10 Replies
View Related
Mar 21, 2006
Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the difference between the data sets.
For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.
View 11 Replies
View Related
Sep 20, 2008
I'm using the following formula to find the 1st date that a particular rep wrote an order.
{=IF(S4="","",IF(MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))=0,"",MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))))}
I also need to figure out when they write their 10th order, but have had no success figuring that out.
View 6 Replies
View Related
Mar 9, 2009
I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
View 2 Replies
View Related
Sep 14, 2011
I am trining to calculate SLA times based on P2, P3 & P4 calls, the only issue is with P2's as the SLA includes the weekend, how to include the weekends into my formula?
AE2 = Priority
U2 = START DATE & TIME
V2 = END DATE & TIME
[Code]....
View 4 Replies
View Related
Feb 28, 2005
how can i change nonworking date from saturday&sunday to frinday&Saturday
View 11 Replies
View Related
