Add To Current Date Based On Criteria
Jan 3, 2007
I 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? ....
View 9 Replies
ADVERTISEMENT
Mar 9, 2014
I'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)
View 4 Replies
View Related
Feb 21, 2008
I am using advanced filter. When I use
Range("S3").Value = "" = "M6" = """"""
I get a runtime error 13. Am I missing something? After reading through other posts, I could not find anything wrong with this.
View 4 Replies
View Related
Jun 20, 2013
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
View 12 Replies
View Related
May 30, 2014
Future 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.
View 2 Replies
View Related
Dec 14, 2011
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".
View 9 Replies
View Related
Apr 29, 2008
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.
View 3 Replies
View Related
Aug 6, 2013
We have a few residential care homes across UK. I have a workbook where I store all our residents fees which I update daily. There are 2 worksheets on one I store list of residents on the other one I have a daily breakdown.
What I'd like to do is to use worksheet "daily" to sum fees from "all residents" with criteria like:
- carehome ("daily" row 2),
- fee ("daily" F:K,N:S,V:AA etc)
- date ("daily" C7:C1073).
While fees are on worksheet "all residents" where:
- column A has carehome name on it
- column M to AG has fees on it
- row M3:AG3 has dates on it
For example id like to count in cell F7 how many residents in Branch Court "Bc" were in residency on 12 March 2012 with £520 fee looking on worksheet "all residents" with criteria fee, carehome,date)
Each time one of the council backdates fees or changes a resident to lets say a private one I have to manually change all the numbers which takes time.
res.xlsx
View 7 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
Dec 23, 2008
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.
View 2 Replies
View Related
Aug 21, 2012
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??
View 6 Replies
View Related
Aug 16, 2013
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.
View 2 Replies
View Related
Jan 22, 2008
how do you calculate someone's age nearest today
View 3 Replies
View Related
Apr 3, 2008
I'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 Related
Apr 21, 2008
I 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.
View 5 Replies
View Related
Jul 25, 2013
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?
View 8 Replies
View Related
Sep 1, 2013
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.
View 1 Replies
View Related
Mar 19, 2009
I am trying to write a formula that will have the cell display todays date if it is after 2:00PM, and if it is before 2:00PM I need the cell to display Today()-1. I have tried several iterations and I'm stumped.
View 5 Replies
View Related
Jul 21, 2009
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies
View Related
Aug 21, 2014
I have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies
View Related
Dec 6, 2013
I have two columns, column Q and R. Column Q has Data Validation set, where you can only choose one of four options in any given cell between rows 2-250,000. When "Complete" is chosen, the cell turns green. Now when "Complete" is chosen in column Q, I need the cell next to it in Column R to auto-fill today's date. However, the date should not update to the next day. So if the cell autofills to today's date, which is 12/6/13, tomorrow, when the date changes to 12/7/13, the cell still reads 12/6/13.
View 4 Replies
View Related
Jan 6, 2013
I have series of data values like below. I have to find Maximum, Minimum values for each of these values.
9430
9822
10070
[Code].....
View 2 Replies
View Related
Nov 3, 2009
I'm trying to create a function in a template invioce that will look up a value in another spread sheet (fees).The data will be based off three values entered in the invioce from drop down boxes. I've attached the workbook with the master sheet and the invioce (sheet 5) The three criteria that can be selected is the project, month and name and I want the amount of hours to be returned based on that information in column e. The projects are seperated and billed by month.
View 3 Replies
View Related
Feb 25, 2014
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
View 2 Replies
View Related
Nov 24, 2006
I wanted to solve a problem in excel. For a particular period there will be flow of units on a daily basis, which can be more or less than actual capacity. Flow received will be done on first in first out basis. I wanted to know the completion date for the same in excel. I'm attaching the sample sheet of the same.
View 7 Replies
View Related
May 15, 2009
i'm trying to write a summary page for the data collect on the first sheet and would like to find a way of counting the number of records within certain date criteria (those that are more than 30 days old). now the crucial part is that the records have a second set of critical data, their Status, and i need to find only the records that have a particular status (in this example that is Pending and Open).
if you look at the second sheet you'll see that i can find records that are associated with a specific date, but not with a range of dates; which is particularly annoying because DCOUNT can find ranges of information when using other types of number.
View 3 Replies
View Related
Mar 20, 2012
I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
View 1 Replies
View Related
Jul 16, 2007
I need to determine the MAX date in a column based on specific criteria. Column A6:A500 = Task Names, Column N6:N500 = Dates (there are some blanks).
What I need is to determine the latest date in column N6:N500 based on which task I choose from column A6:A500.
View 9 Replies
View Related
Jan 25, 2010
Ok so let me set this up. I have 2 columns: .....
View 14 Replies
View Related
Dec 2, 2008
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
View 9 Replies
View Related
