Using Date To Return A Value?
Feb 28, 2014
I have a spreadsheet that is imported from Quickbooks Pro 2012, and it comes over with the following date format:
01/27/2014
I need a formula that will read that and return the value of 25th. To explain, any invoice that is created between the 1st and the 10th of any given month should return the value 1st, any invoice created between the 11th and the 20th should return 15th, and any value returned between the 21st and 31st should return 25th.
Where I'm at:
I'm assuming I'm going to want to use an IF function. Since the digits will always return in the same spot (the 4th/5th spot), I know I want the formula to return the value based on that specific value. I have the following formula that provides similar information/functionality, I'm just not sure how to edit it to return those values.
//=IF(LEFT(D14,1)="I","INV",IF(MID(D14,3,1)="-","CC","ACH"))
I have this formula that does something similar to what I want to accomplish, I'm just not 100% how to edit it to find the value that I want, and then return the value that I want. I guess the biggest issue is that I only understand about 85% of what this formula is trying to accomplish.
View 14 Replies
ADVERTISEMENT
Jul 1, 2008
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
View 2 Replies
View Related
Feb 21, 2014
I want to get a return value of date in column C whereas column A represent a date and column B represent a number. Simplest formula in excel is Column C (Feb 26, 2014)= Column A (Feb 21, 2014) + Column B (5). I want it to have in macro code.
View 14 Replies
View Related
Dec 8, 2009
I am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
View 6 Replies
View Related
Apr 24, 2013
Using one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
View 3 Replies
View Related
Jul 16, 2009
find the expression that will return the numeric value of a date in a cell not the actual date. ie date value cells(1,3)
View 2 Replies
View Related
Jul 31, 2014
If I make a sale between the 1st of each month through the 1th of each month I will be paid on the 31st of that month. If I make a sale on the 16th of each month through to the last day of each month I will be paid on the 15th of the next month. I need this formula to work for every month.
cell e has date of sale
cell h has the returned value of the formula above
View 2 Replies
View Related
Mar 10, 2014
I have the attached example workbook and I'm trying to lookup a date from a list based on a project start month:
- in cell D8 I want to be able to return the date that corresponds to the project month in row 5
- the dates are in column A
Example WB 1.xlsx
View 3 Replies
View Related
Jan 13, 2009
how to get a date to return what the actual day? ie 13/01/09 is tuesday?
View 3 Replies
View Related
May 25, 2006
I have one worksheet with 52 rows for each week of the year with the
following headings: week #, start date of week and end date of week. I have
another worksheet where I need to forecast expected expenses for new
employees based on the date they start ie. Joe starts in Feb 6 (week 6) and
then calculate expected spend based on remaining weeks in the year. I do
this manually right now. How can I automate this?
View 10 Replies
View Related
Feb 6, 2012
Okay - the variable myDTStart is calculated off of this formula and I've put in what I have it testing as now and the result I am getting:
Code:
myDTStart = Application.WorksheetFunction.WorkDay(MyDate, (myBMLT - myAssy - OMSpd), holidays)
MyDate = today's date (coming from a cell - current value of 2/6/2012)
myBMLT = # of days (coming from a cell - current value of 15)
myAssy = # of days (coming from a cell - current value of 3)
OMSpd = # of days (coming from a cell - current value of 1)
holidays = refers to a range of cells that has holidays (Dim as range)
It is currently returning it as a "40998" , but not in the right format in a msgbox. How do I get it to return it in date format "mm/dd/yyyy"? I've tried doing it with a With statement and a Format statement, but can't get it to work.
View 3 Replies
View Related
Jun 27, 2012
I need to return the date of the third day of the month following the date in cell a1:
So, If A1= 06/01/12
A2 should = 07/03/12
View 2 Replies
View Related
Nov 8, 2012
In worksheet1 I have a set of data. Column A is a value, column B and C are dates. In worksheet2 I have dates in Row 1 going out horizontially. I would like to create a fomula that returns the value in column A in worksheet1, if the date is between column B and C. I couldn't get my array to work. it's been a couple of years since I've wrote any indepth formulas.
View 7 Replies
View Related
Apr 10, 2007
Looking at a series of rows that contain dates (in one column).
Return the value of the cell in the column next to the most recent date.
I can get the most recent date using MAX, but how do I tell Excel to return the value of the cell next to it?
View 9 Replies
View Related
Jan 13, 2009
Hi, i need a formula that will return date for Wednesday each week, but if Wednesday is public holiday then return date for next business day
View 9 Replies
View Related
Dec 4, 2009
I need A1 to return todays date if B1 has a values of more than 1
So would be something like
=IF(A1>1),NOW()
View 9 Replies
View Related
Aug 1, 2009
Need to convert price on transactions throughout the year to different currencies - using historic currency exchange rates.
Entered data in columns A-D:
Date1 | Value1 | Date2 | Value2 |
Required Formula to generate column E for each row individually (to 2dp using d/m/y format):
Value1xValue2whereDate1=Date2
Date1/Value1 will be prices on certain dates while Date2/Value2 will be historic exchange rates for every day of the year.
View 9 Replies
View Related
Sep 13, 2007
Is there a end of "week" function which shows only the date the friday? says ..if today is 9/13/2007, the cell will show 9/7/2007...
View 3 Replies
View Related
Oct 16, 2007
I'm after a formula(s) which will give the start date and end date of a month using the month name. For instance, if cell A1 contained OCTOBER 07 the formula result for the start of the month would be 01/10/07 and the formula for the end of the month would be 31/10/07. I have plyed around with the EOMONTH function but this only workd when an actual date is entered.
View 8 Replies
View Related
Mar 7, 2008
I'm trying to create a dynamic data table which has dates (in reverse chronological order) going down column A.
I want to find the value for a commodity (say bananas) which is housed in a master data table, based on the date that's in column A.
View 9 Replies
View Related
Jun 18, 2008
Suppose I am having Emp codes in Column A and BirthDate(dd/mmm/yyyy) in Column B in a excel sheet. I want to find out the employee whose birth date is on the current date i.e today. Is there any function wherein one can use FIND Function with IF Condition ?
View 2 Replies
View Related
Mar 28, 2014
I have a list of product IDs in one col. In another 2, I have date and vendor that each items was sent. Note that each items can be sent several time to different ven for at different date. From this list I would like to build another list that shows me WHEN & WHERE each item was last sent.
I was able to find a formula to return a list of unique item for my product IDs, but get stuck on the next step.
View 5 Replies
View Related
May 25, 2014
I am trying to return start and finish date of events depending on when resources are allocated.
View 4 Replies
View Related
Jan 7, 2014
a column contains dates in date format, contains a date before the column b date b column contains today() I want c column to get some output, if b-a is more than 5 years (exact days), say 20 do , if b-a is more than 2 years and less than 5 years, say 40 do , if b-a is less than 2 years , say 60 otherwise blank use if condition to get the above results. I have formatted a, b with date formats
View 9 Replies
View Related
Jan 16, 2007
Is it possible to to return a date that begins on following mondayand adds days unless it was entered on a monday then just add 17 days
if someone enters a date say 10/01/07 (wednesday) i need to be able get the following mondays date and add 10 days to it IE 25/01/07
View 14 Replies
View Related
Sep 7, 2009
the last date input into a range covering multiple columns and return the date in the cell containing the formula):
{=index(a9:n9,1,max(if(cell("format",a9:n9)="D4",column(a9:n9)))-column(a9)+1)}
My column headings use this pattern:
Column A: Date
Column B: Amount
Column C: Date
Column D: Amount
Column E: Date
Column F: Amount
and so on.
When I use the above formula, I end up with data from the last amount column instead of the last date column.
View 9 Replies
View Related
Oct 9, 2009
column a has dates & column d has values - what is formula to find highest number in values and return it's date (using =large(Dxx:Dxxx, 1) to find highest number
View 2 Replies
View Related
Dec 9, 2009
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View 5 Replies
View Related
Dec 7, 2012
I need to get dates from one file to another. They can be matched on ID.
So if the ID for a record from FileA matches the ID for a record in FileB, return the date from the date from the same row in FileA to FileB. I would just sort and copy them over, but FileA has more records than FileB. All distinct IDs share the same date.
View 3 Replies
View Related
Apr 17, 2014
Someone will enter their financial year end in the worksheet e.g. 31/03/2014 and I then have 12 cells below it called Month 1, Month 2 etc up to Month 12. I need the cell next to Month 1 to calculate what it would be... so for a Year End of 31/03/2014 month 1 would be April and this needs to apply to the 12 months.
View 2 Replies
View Related
