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.
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedDATE function won't return TODAY()'s year in the "year" slot.
Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?
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)
I currently have a spreadsheet with 500ish renewal dates. I need to be able to take the renewal dates (dd-mmm) in column B and then display the current year with renewal date, if the date has not yet come, or the next year with renewal date, if the date has already passed in column C (dd-mmm-yy).
View 3 Replies View RelatedI am trying to get mmm-yy date format for the next 2 consecutive months from now. I used the following code for that reason, but I am not getting.
strDate = Format(DateAdd("m", 1, Date), "mmm-yy")
strDate = Format(DateAdd("m", 2, Date), "mmm-yy")
However if I use this, I get the current months date correctly (like May - 08)
strDate = Format(DateAdd("m", 0, Date), "mmm-yy")
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.
I've got a formula that has numerous nested IF statement and am trying to get the true result to be the first of a given month of the current year. Here's my formula:
Code:
=IF(Setup!$B$3="X","1/1",IF(Setup!$C$3="X","2/1",IF(Setup!$D$3="X","3/1",IF(Setup!$E$3="X","4/1",
IF(Setup!$F$3="X","5/1",IF(Setup!$G$3="X","6/1",IF(Setup!$H$3="X","7/1",IF(Setup!$I$3="X","8/1",
IF(Setup!$J$3="X","9/1",IF(Setup!$K$3="X","10/1",IF(Setup!$L$3="X","11/1",IF(Setup!$M$3="X","12/1",""))))))))))))
Everything works, save that the cell becomes 1/1 with no year at all. I've messed around a bit with TEXT and various DATE/MONTH/YEAR functions on this one, but can't seem to get it correct.
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 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 RelatedIs there a way to make a cell display today's date as YYYYMMDD?
Assuming data!a2= 7989, I need the following formula to display as shown below
="txt"&data!a2&"txt"&today
displayed as
txt7989txt20080507
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.
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've got a spreadsheet that gets its information from outside of Excel. The Date comes across as a number stored as text. I need to some how calculate the amount of days between this imported date (Settlement Date) and Today's date.
View 4 Replies View RelatedI 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 RelatedI am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I have a macro which will import data to the worksheet, then perform some formatting on the data, then assign the month & job description based on the lookup table. The problem is that when I import in the data, the data in column B&C will be store as text instead of number and the date in column E will store a 2 digit year instead on 4 digit year which cause error to my macro. I have try to preset the column format to number, i even try to change the column format to number when i run the format macro data. But the problem is still there.
View 5 Replies View RelatedI want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.
There is of course several ways to do this, but is there a simple and easy formula one can use.
formula off here i use all the time relating to finding and sumproducts for specific months and years i.e.
Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula...
is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009?
The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.
My spreadsheet uses alternating columns for actual and budget inputs
I would like to preload the budget for the whole year
But the ytd formula should only show results to match the input of actuals
In otherwords, If I input actuals for period 1, the YTD formulas for actuals and budget should return YTD period 1 only and ignore the remainder of the budget inputs
After period 2 actual inputs, the formulas shlould update to reflect period 2 YTD and so on and so forth
How can I accomplich this?
Sample of layout attached
Totals at Cols AK-AM
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? ....
I have a column with dates of July 1 of every year. How can I use conditional formatting to shade a cell a certain color based on July 1 of every year? The cell for July 2013 should be shaded, when 2014 comes, it is no longer shaded, then 2014 becomes shaded, & so on...
View 1 Replies View RelatedHow I can subtract a year when I formulate based on if the current month is January.
Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.
I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.
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.
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?