Finding The Quarter Period Of A Date
I'm trying to find out the date quarter of a given date in cell A1.ie Q1,Q2 etc.
I've found the code below to work out fiscal quarters.I thought if I dropped the +1 from the end of the formula it would work out the quarter in non fiscal terms. This however only works from months Jan to Nov. December returns a zero.
View Complete Thread with Replies
Related Forum Messages:
Calculate The Quarter To Date Number
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3-AL3.
First - can anyone explain the formula so I may understand it and possibly modify it for other uses
Second - wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3 - any suggestions on how to do this easily.
By the way, the formulas are in column AO, staring on row 4.
Report On Date Period And Subtotal
I have attached a sample of my spreadsheet to use as a reference. I suggest you open that up and give it a look through to get an idea of what's going on.
What I'm looking for is a way to create a report (the quickest and easiest way of course, as always) which will give the total for the columns 'Exit Weight', 'Net Weight' and 'Entry Weight' sorted by each company between the periods of the 25th of each month to the 24th of the next month (the Dates are in Column A obviously)
Below is an example of the output I am looking for:
Company: Period: Exit Weight: Net Weight: Entry Weight:
EASTWELL HA.. 25/10/07-24/11/07 102.2 124.3 200.14
25/11/07-24/12/07 143.7 185.2 250.18
25/12/07-24/1/08 ... ... ...
BOURKE TRANS.. 25/10/07-.... ... ... ...
the totals for the Exit Weight, Net Weight and Entry Weight are not the actual totals, they were just put in to give you an idea of what I'm talking about.
And on and on. If there is a way to automate this with Excel VBA and some sort of code that you could give me to plug into my spreadsheet I would be very appreciative. I understand this is a bit complicated and would probably require a few steps so if don't get the answer I'm looking for I'll probably find a way to split it into steps and then ask the questions seperately.
Calculate Future Dates From Start Date With Varying Time Period/cycle
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
Finding Current Date
I have a sheet that keeps accrued hours weekly for 52 weeks. I would like to show a in another sheet what the accrued hours are for the current week.
The 2nd column for each week is in text format "6/1/09-6/7/09" and the last column shows the accrued hours for that week. I need to know how to find the current week from the text format?
Finding A Date Value On A Worksheet
Is there a simple way using VBA to find a date in the format of dd/mm/yyyy? I have a range on every sheet from A1 to IQ1 with dates already entered. I want to add this vba to some other code so the user can go straight to the date entered on the worksheet.
Using the edit find, it can find dates easily. Can this function be replicated through VBA?
Finding Oldest Date
date1/Jan2/Jan3/Jan4/Jan5/Jan6/Jan7/Jan8/Jan9/Jan10/Jan11/Janquantity 502023108948202535current stock 200oldest date of manufacture?
I have a report that lists the quantity of an item that is manufactured on particular day. On some days there will be no items produced. The report is similar to the above table. I don't want to change the table format.
I know the current stock of that item (in this example it is 200). Note that the current stock is not just the sum of the quantiy for each day's production.
I need a function that will determine the oldest date of manufacture of material in the current stock. The function assumes that that the current stock is made up of the most recently produced material. ie the stock is depleted on a first in -first out basis. Essentially the calculation has to sum backwards from the most recent manufacture date until the total = 200 and then lookup to corresponding date.
In the above table the oldest prodcution date would be 6/Jan. The answer to the funstion in example = 6/Jan
Finding Date Formula
i have range u1:v25 and one cell out of this range will contain a date, the problem here is that the date is formatted as text and is possibel to have some text befor or after or both. also the date will never be in one format,
here is how it could look like
commets is any posible text.
comments 10/12/08 comments
so all i need is just the date in the same format its in or in any other format. as long as i get the date.
Finding Closest Date In A List
I've been turning this problem over it my head for the last day and can't seem to come up with a decent solution, so I figured I'd post it here to see if anybody else had any bright ideas.
I have a data sheet with a dropdown list on it to select the month and year that determines how the rest of the data sheet populates. Basically I'm giving weekly breakdowns of data for a month at a time, with the "Week Ending" heading (which are dates - Saturdays), populating off of the month and year selected from the dropdown.
So, to give an example, if somebody selects March 2008 in the dropdown, it will populate the week ending dates of 3/1, 3/8, 3/15, 3/22, 3/29, and then pull out the specific data for each week based on those dates.
What I'd like to do is write a formula that will find the first Saturday of the month. The way I have it jury-rigged right now is a list of months with the first Saturday listed next to it and a vlookup to grab the date of the first Saturday. I envision using this report indefinitely into the future and I'd like to avoid having to keep adding/changing the manual list of Saturdays, instead preferring to have a way to determine the date.
Finding Date And Position Cursor
1-Using the Date-Time data in cell T3 find the cell in column A that has the same Date and time.
2-Position the cursor in column I of the same Row.
In the example giving the Date-Time in T3 is: 07/03/2008 17:15:00. The Cell in column A that has this same Date-Time is: A97. The cursor should then be positioned in Cell I97.
Vlookup For Date Not Finding It. #N/A Error
I haven't had issues with vlookup on other worksheets, but this one is giving me trouble. I have attached the excel file for your consideration.
The lookup value is in cell U2, formatted as a date
The table array is V2:Z19
The column index number is 2 for C3
I've tried the formula with and without the text function for the date and I've also tried different formats for the dates in columns V to Z because I know the format of the cells have to match for vlookup. It is very frustrating because this should be simple, but it is messing up.
Finding The Logon And Logoff Time For That Date
I have one monthly file which is in the attached format file name "Example".
I need the data from this file as per the format mentioned in the file "must be in this format".
from the "example.xlsx" file i need the login time (only first login) and Logoff time (only last logoff time) which is there in the description column. It must be available in the format mentioned in the file "must be in the format.xlsx".
Finding Date Within String (hyperlink) And Pasting Value
I have a sheet which contains a folder listing of files with the filename displayed as a hyperlink.
I would like to have a formula which works out a couple of values based on the file name which has been copied into a given cell as a hyper link.
In my sheet Column F contains the file names (as hyperlinks)
e.g. Anab AM1 2006-02-27.pdf
or CD 01_02 2006_05_18(2).pdf
I'd like to create a formula for Column A which will give me the first 2 blocks of text which are seperated by a space (in the examples above it would be "Anab AM1" and "CD 01_02") Anything after the second space should be ommited (including the 2nd space itself)
I'd also like column B to find the date within the filename. Note this is not always the rightmost part of the filename (excluding the .pdf) I've worked out I can use the Find function to find the '2006' (I'll always have the same year in here as my folder listings are sorted by year) but it returns the character where 2006 is located e.g. for "Anab AM1 2006-02-27.pdf" it gives me 10.
My date formats are always in the form yyyy-(or_)mm-(or_)dd
Finding Atribute Last Accessed Date Of A File And Make It A Range Value
I want to place the atribute (Date as value): "LastAccessDate" of a file called "!FileSessionStart.nwd" in Sheet1.Cell A2 in my workbook - without opening/accessing" this "C:/so-and-so/!FileSessionStart.nwd" - just want to read and use the last accessed date of it.
How will a macro go finding this atribute of this particular file and place this atribute as value as described?
The filepath of it is valued in celle A1
Finding Data That Has 2 Work Orders And Comparing With Completion Date
What I am looking for is a macro/formula that will look at a unit number, then the type of work done, then the completion date and decide if the same type of work was done on the same unit within a 7 day span of time.
Unit is 11111, work is plumbing completion date is 10/4/07
Unit is 11111, work is plumbing completion date is 10/7/07
This means the work has been done 2 times (obviously showing that it did not get fixed the first time)
Is there any way to mark an X in the square with the formula, or any type of identifier what-so-ever
I have attached a spreadsheet and hope it helps
Again I know this is complex so it is OK if no one feels like messing with it but I wanted to check here first.
Determining Quarter And Year
In cell A1 I have a date entered as text as "Apr 2007". (That's the way my tool pulls it. Format can be changed if it helps)
I was able to pull the Quarter and year (Q2 2007) using...
A2 ="Q" & ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)
I need to pull the next three quarters and their year. (Q3 2007, Q4 2007, Q1 2008)
End Of Quarter Function
Need function to calculate the end of quarter for a given date after adding any number of months to that date. Example cell A1 would contain 01/15/2010. I want to add 6 months to A1. A2 would contain 6 making it 07/15/2010, B1 would contain a function that would display 09/30/2010.
How To Calculate Max Profit Per Quarter
Formula to get the Quarter number for the highest profit per Product, like in B16:F16.
The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums.
I know how to find the qtr. Number - like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.
Rounding Timesheet To Quarter Hour
I have made a time sheet and am trying to have the total hours and grand total- round up to the nearest quarter hour, I.E. (.25, .50, .75. 00), if anyone can help me please it will greatly be appreciated, this is what i have now, in my totals fields:
I Have also attached the file so you can see it completely.
Constant Line In Chart By Quarter
I have a bar chart that has data by the quarter over a span of 3 years, so there are a total of 12 bars on the chart. Everything looks good there, but now I need to add the minimum amount that the customer needs to acheive (which varies every quarter) in the form of a line. So I added a line chart with the minimum data, but the problem is it goes up and down from point to point and doesn't create a visual horizontal line for the customer to see if they are hitting their minimum every quarter. Is there any way around this?
Average Number Of Days In A Quarter
I have a worksheet containing 5 columns. A start and end date, a number of days between these dates, the quarter number of the start date (i.e. 1 to 4) and then the year.
I want to calculate the average number of days per quarter so an average of days in column C, based on the details in column D and E.
Rounding To The Neartest Quarter Hour
I have searched around and have found several formulas designed to round to the nearest 15 min interval, but none seem to be working for me. If anyone has some insight.
R1 Main Task Total in Hours
R2 Sub Tasking 1 30 min
R3 Sub Tasking 2 6 min
R4 Sub Tasking 3 5 min
R5 Sub Tasking 4 23 min
What I need to happen is for the total of the time allotted for each tasking to be added and rounded to the nearest 15 min interval in a hour.min format. For instance, the above would be rounded to 1.25
71 mins total, rounded to 1.25 because .25 is one quarter of an hour in decimal form. I need it to be this way to be able to multiply that by a charge amount for a total cost for the Main Task. The numbers in A2 will all be whole integers.
Identify Top Quarter Of Data
I have been easily identifying the bottom quarter of data using =IF(G2="","",IF(RANK(G2,G$2:G$100,1)<=INT( COUNTA(G$2:G$100)/4),"Yes","")) Now I need the top quarter and am not sure how to change the formula.
I have tried changing <= to >= but I then get the top 75% rather than 25%.
I have tried adding +50% and +.5 ... =IF(G2="","",IF(RANK(G2,G$2:G$100,1)<=INT((COUNTA(G$2:G$100)/4+50%)),"Yes",""))
Identify Bottom Quarter Of Data
I have a column of data that comes from simple division formulas, E2/F2. I am wanting to take the Percentages given and quickly recognize the ones in the bottom quarter.
8 items - the lowest 2 (the bottom quarter) should "stand out" - 5% and 23%
I figured two options:
1.) Conditional Formatting - don't know what the formula would be to use
2.) If statement - preferred method.
I would like in the cell next to the percentages for there to be a blank cell if the amount is not in the bottom 25% and if it is I would like the cell to read "Yes"
I thought something along the lines of =if(G2 in range(G2:G100)is bottom(.25),"Yes","") Of course that is not real code. I'm hoping someone will be able to edit that to make it functional. Or guide me in the right direction.
And you may have noticed the "N/A" in the list above. I would like excel to view this as 100%, at the least just ignore it and don't give me an error.
Formula To Return The AVERAGE By Calendar Quarter
I'm trying to write a formula to return the AVERAGE by calendar quarter, IF
the quarter has ended.
For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10.
But, if Mar was 0 (only meaning no activity) I still need it to calculate.
The formula I started with is -
This works only if cells A1:A3 have a value greater than 0. How can I write
the formula so that as long as A3 is not blank that it will return the
Aligning Rows: Line Up Accounts Across Each Quarter
I run reports from Accounting systems that reflect different periods. Of course each period ends up having more or less accounts. See the attached file. This is just one little sample of big massive reports. I need to line up accounts across each Quarter. so that i can calculate differences between each quarter.
In the past few times, I have done this manually. There has got to be a way to do this quicker. I have tried Pivot table. but pivot table didn't help either (unless i was not doing it right).
Lookup Function (view Data For Any Quarter Of Any Year)
I have spreadsheet that I use to display quarterly metrics. Within the workbook, there are two spreadsheets; (1) output (2) data.
Part 1: I want the ability to view data for any quarter of any year. In columns O-P, I have created a dropdown for the year I want to view and option buttons for the quarter I want to view.
The data is organized in one sheet and includes all information by quarter.
As an example, say I want to display data for District 112 and I want to display data for the 3rd Quarter of 2007. I am trying to create a formula that would look in the table as follows: (1) look for district 112 then (2) look for 2007 then (3) look for 3rd quarter then (4) look for units sold. I tried using a modified lookup function but didnt have any luck.
Part 2: The second part of my question is similar to the first part but in this case I need to summarize data (cells $B$18:$D:$22).
As an example, say I want to display the total number of sales in the U.S. for the 3rd quarter of 2007. In this example, I would look in the table for the following: (1) look for Division then (2) look for 2007 then (3) look for 3rd quarter then (4) sum all based on criteria.
Combo & List Box To Set Calendar Quarter
I need to define a specific calendar quarter and year, and want to do so using a combo box for the quarter (e.g. 1st Quarter, 2nd Quarter, 3rd Quarter, 4th Quarter) and a list box for the year (so the user can select any year desired).
The selections in each item should then somehow define the quarter's date range so I can use it in my SUMPRODUCT calculations.
User selects '1st Quarter' from the Combo Box
User selects '2004' from the List Box
CurrentQuarter range is somehow defined to equal 1/1/04 through 3/31/04.
If this isn't possible, then perhaps two ranges can be defined based on the user's selections:
QtrStart is set to 1/1/04
QtrEnd is set to 3/31/04
IF Statement To Display Quarter Of A Year Based On A Three-letter Month Abbreviation
I'm trying to wrtie an IF statement to display one of Q1, Q2, Q3 or Q4 based on the three letter month abbrev. All i have so far is the following, which isn't leading anywhere - and i have a feeling theres an easier way to write it.
Chart Over A 24 Hour Period
I'm attempting to chart 3 series over a 24 hour period (8am-8am). The 3 series are captured in 1 minute intervals. My X axis intervals is displayed hourly though. My issue is, charting goes bad at 00:00:00. i.e. it stops.
Here are the values I have on my X axis
cross at: .33333
Any ideas how I can get from 8am to 8am?
Percentage Increase In A Period
I'm doing research with a professor at the University of Florida. This is my first time using Excel to any extent, so I need everything from the ground up!
I have the housing values for each of the 50 states from 1975 to 2006. I need to analyze each state individually to see if there were any points during that 26 year span when housing values increased by more than 20%. I need to know when this increase started and when values peaked and began to fall. I could do this manually, but I'm assuming that would take a while.
Is there a simple way to do this in excel? A macro, or a UDF (I have no clue about either of them by the way).
The 50 states are listed in a column and the rows next to each state having housing values for the 26 years. ( I don't know if that is relevant).
Calculating If A Period Is During A Workday
I have two dates in this format:
start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00
i need to check if the period in between those is during a workday.
our workday starts at 10:00:00 and finishes at 19:00:00
so my expected result is 3 hours. (1400-1100)
start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00
result is 1900-1100 + 1100-1000
= 9 hours
because it is during two workdays...
is there a way to calculate this in VBA?
Calculate Absence In A Period
how i would calculate the number of sickness days and occaisions in the previous 26 week period from todays date.
I've attached the spreadsheet.
So in C4 i want to have the total number of times(occasions) the person has been absent in the previous 26 weeks.
and in E4 i want to have the Total number of Days (taken from E7:E20) the person has been sick in the past 26 weeks
Sharing The Value Of Job Over A Certain Period
i need to share the valve of the job over the months. see attahed excel sheet. say the job worth 10k has a start date of the 30th of nov and the end date is the 1st of dec. the value of the job is shared between two days and then put into the relevent cell. as regards to the months they are not calander months so the start date and end date of the months are diffrent see attached sheet for full details.
Period Replaced By Colon
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.
Time Trial Period In My Spreadsheet
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times.
I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between $20 and $60....
Calculating Time Across A 24 Hour Period
Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.
In B5 Startime is 22:00
In C5 Finishtime is 06:30
In I3 increment begins at 00:00
In I4 increment ends at 00:30
The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.
Work Out The Value Of Cars Over A Period Of Time
I have a project where I have to work out the value of cars over a period of time.
The cars depreciate at 36.9% per year over a 5 year period and I can't work out for the life of me how to create a formula in excel that give me the values at the end of each year.
Each car costs £10000 and I need to know what the value of each car is at the end of each year