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 RepliesI'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
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 RelatedFuture Date Sample.xlsx
I have attached a spreadsheet with a formula I'm looking for. I tried HLOOKUP, INDEX & MATCH, And others, but I was nowhere near what I needed.
I have a report that in column A shows a ship date. These ship dates may be "Previous","Same Day", "Next Day", or "Future".
I need VBA code that will look at the day in column A and determine if it matches the current date on the computer if it does then in column W in the same row I need the string "Same Day".
If its the systems date plus 1 day column W in the same row would state "Next day".
If its the systems date plus 2 or more column W in the same row would state "Future".
If its any date prior than than the current system date then column W in the same row would state "Previous".
I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!
Cell A1 contains a Date eg (01/01/07)
In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.
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 Relatedhow 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
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 )
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 RelatedI am trying to get a function to return a date based on the string in the preceding cell. If the cell, say A1, has the string "Before 12:30", i want the cell B1 to put the date 5 days from now in the cell. If there is any other string entered then 6 days from now. I also need it to include the networkdays function.
Here's what i've got, messy i know but functions aren't my strong point. Also would i save file size if i did this in VBA? ....
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 RelatedOn 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).
I have tried to record a macro and change it to what I need but so far I have not been able to make it work.
I would like to have a macro that will auto filter on colomn 1 in the following way.........BETWEEN >= Today -10days and Today.
I have the formula for calculating a 13 week rolling average, but I need to be able to set goals in the future and still have the average calculate from current and not from future.
I have my dates set up in row 3 (these dates are all mondays, not every day) and my data in row 4. I need the 13 week rolling average to calculate starting from the prior monday and going back 12 more mondays for the 13 weeks. IE: This monday I'm running reports that the info will be entered into the previous week's monday.
Here is the formula that I'm currently using: =AVERAGE(OFFSET(K4,,COUNT(K4:BV4)-13,,13))
The formula works but will calculate the average for 13 weeks prior based on the last cell with data in it. It's calculating the average of the last 13 weeks of next year.
how to make this work without having to remember my weekly goals every week??
I'm trying to find a formula that will allow me to show a value based on whether certain dates have passed. This is for the purposes of calculating PTO hours and removing unpaid holiday hours from the equation.
I've got "slave" cells set up with the dates and amount of hours for each holiday, but I'm having some trouble.
I've been trying something to the effect of:
=IF(B4>B14,8 IF(B4>B15, 13))
Now, I can get it to show the "8" if I keep only the first IF function, but adding additional F functions like in the formula above only returns errors.
My goal is to get B7 to show the sum (through formula or manual entry) of C14-C21 based on whether B4 [=NOW()] is before or after the corresponding date in B14-B21.
I need to clarify any terminology or other cell values.
how do you calculate someone's age nearest today
View 3 Replies View RelatedI'm trying to link three cells lets call them C7, E7, and G7 to an NBA schedule. I'd like to have C7 Display the visiting team, E7 to display the date, and G7 to display the time of the game. Ideally I'd like these cells to change after the date of the previous game has passed to the next game. Is this possible? Would I need to create a separate sheet with the Visiting team, date of game, and tiem of game?
View 3 Replies View RelatedI am using the following formula:
=DATEDIF(F7,TODAY(),"Y")
I have a about 1200 rows with a column of student's birth dates. I need to convert the dates to ages and have them inputted in the next column Ages. I ran the code in the 'Age' column and it worked for the first birth date. But I do not know how to copy the code to the rest of the rows so it will convert every birth date in each row.
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.
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]
Sheet1 is a report that expresses 90 days of information and Sheet2 has three columns of 365 day information. Sheet1 has a title, a today function for the date (cell B2), two columns (A5:A94 and B5:B94), and then some other info not relevant to the problem. Sheet2 has three columns (A1:A365, B1:B365, and C1:C365) with the date in column A starting at the first of the year and then two lists of information in columns B and C. I am wondering if there is any way excel would be able to recognize the Date (B2) on Sheet1 and be able to pull the information from Sheet2 starting on that date for the next 90 days.
For example:
My report always starts on the day I am accessing it. So... Say it is 1/15/13. My today function in Sheet1 (B2) would express 1/15/13. This date would be equal to A15 on Sheet2. So I would want A5 & B5 on Sheet1 to return B15 & C15 from Sheet2 respectively.
A5 Sheet1 = B15 Sheet2 & B5 Sheet1 = C15 Sheet2
A6 Sheet1 = B16 Sheet2 & B6 Sheet1 = C16 Sheet2
...
A93 Sheet1 = B103 Sheet2 & B93 Sheet1 = C103 Sheet2
A94 Sheet1 = B104 Sheet2 & B94 Sheet1 = C104 Sheet2
With that being said, the next time I open up my report, say a week from the 15th or 1/22/13 (expressed by my today function in B2), I would want A5:A94 & B5:B94 to reference the new set of information:
A5 Sheet1 = B22 Sheet2 & B5 Sheet1 = C22 Sheet2
A6 Sheet1 = B23 Sheet2 & B6 Sheet1 = C23 Sheet2
...
A93 Sheet1 = B110 Sheet2 & B93 Sheet1 = C110 Sheet2
A94 Sheet1 = B111 Sheet2 & B94 Sheet1 = C111 Sheet2
Is this at all possible?
I'm having trouble with a complex Excel formula. I'm calculating child support owed based on the current date coupled with future child support owed. My columns are as follows:
Debt / Future Amount Owed / Monthly Payment / Total Amount Paid
10,148.72 / 83312 / 508 / 1000
On the first of each new month, the Future Amount Owed should decrease by 508. Altering the Total Amount Paid should decrease the Debt column. Progressing another month without altering the Total Amount Paid column should add 508 to the Debt column while subtracting 508 from the Future Amount Owed column.
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
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
I have userform with date pickers and have text boxes overlaid on these, when I select todays date from the date picker it does not display the current date in the text box (I have 8 date pickers on the userform). If I select another date then reselect the current date it works. It has occasionally worked but why.
Below is the code for populating the text box from the Date Picker.
Private Sub DTPicker1_Change()
TextBox1.Value = DTPicker1.Value
End Sub
The initialize userform code uses the following to format and set the textbox
Code:
TextBox1.Value = Format(Date, "dd-mmm-yy")
TextBox1.Value = ""
Windows 7 with Excel 2010
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.
I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.
Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.
I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.
this was the macro that was created
Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select