Finding Everything That Comes After The Last Period OR Comma?

Feb 25, 2013

I have a work book that has about 1200 entries. I'd like to grab everything that comes after the last comma or the last period in the cell. Can this be done?

View 4 Replies


Removing Chars Not (a-z,1-9,space,comma,period)

Dec 17, 2009

I have a file of names that has some undisplayable characters. I am trying to match against a different file. It works if I overtype the "blank" fields with a space. How can remove these trailing problem characters whatever they are? I tried CLEAN and TRIM and the mystery characters are still there.

View 2 Replies View Related

Finding The Quarter Period Of A Date

Mar 29, 2007

I'm trying to find out the date quarter of a given date in cell 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 6 Replies View Related

Finding Text, Seperated By A Comma?

May 10, 2009

Is it possible to find text listed in a cell, that has sereral options seperated by commas?


red, green, blue, orange, purple, black (all listed in one cell)

black is or is not found in targeted cell?

View 6 Replies View Related

Lookup Comma Separated Data And Return Comma Separated Answer

Mar 19, 2014

Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.

Sample file attached : Book1.xlsx

View 3 Replies View Related

Sharing The Value Of Job Over A Certain Period

Nov 13, 2008

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.

View 2 Replies View Related

Last Occurrence Of Value In A Period

May 26, 2014

I need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.

I have tried using array formula


View 5 Replies View Related

Using SUMIF To Calculate SUM Over 30, 90, 365 Day Period

Oct 7, 2008

I have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength))

FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.

View 3 Replies View Related

Calculate Absence In A Period

Apr 28, 2009

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

View 2 Replies View Related

Highlighting Dates After A Set Period?

Aug 1, 2013

I have a sheet i am working on at the moment, i basically have in Column A (named Interview Date) a date and in column Q (named Date Cleared) another date.

What i want is so when i enter a date in column Q if the date is 14 days or more after the date in column A it will highlight Blue and if the date is upto 14 days after the date in Column A it will highlght green.

View 2 Replies View Related

Calculating If A Period Is During A Workday

Jul 22, 2009

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)

other example:

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?

View 14 Replies View Related

How To Sum Interest During Calendar Period

Oct 13, 2011

How can I find the total of interest (or and expense) during a given calendar range

Data is as follows

Date Type Amount
4/1/11 Interest 4.00
4/15/11 charge 22.00
5/1/11 interest 3.00

I want a cell to compute the interest in april only.

I have tried =sumif(range, end date, amount_range)- sumif(range, beg date, amount_range)

but how do I nest the type argument into the argument

Possible approaches advanced filter? and function? add another column that uses an if statement to do interest only and do the sumif statement above I was hoping to have a drop down box that could have interest, COGS, beside it and april, may, june above it

View 1 Replies View Related

Adding Period(s) To Initials

May 5, 2012

I am trying to get periods added to initials.

Colum A
(Can be up to 4 caracters)

What I need in colum B


What formula can I use in the B cells to get this result?

View 7 Replies View Related

Web Query Adding Period To URL?

Jul 1, 2014

I'm trying to do a very simple web query drawing a table of holidays based on what country you select.

I'm using this very basic parameter setting

http://["Web Address"]


I set up the connection and when I add the country (using a separate cell and a concatenate) the URL looks ok in the cell ( but the query returns an error saying it it's unable to open ""

View 2 Replies View Related

Chart Over A 24 Hour Period

Mar 13, 2008

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

min: .33333
max: 1.35
maj: .04167
min: .00347
cross at: .33333

Any ideas how I can get from 8am to 8am?

View 9 Replies View Related

Percentage Increase In A Period

May 30, 2008

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).

View 9 Replies View Related

Period Replaced By Colon

Oct 3, 2006

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.

View 2 Replies View Related

YTD And Period Sales Report

Feb 2, 2008

I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format.

The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only).

For the current Year- period (month) and YTD (only up to the period displayed).

For the last year- period and YTD (only up to the period displayed).

The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales

I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck.

I would also like to be able to select which period I am viewing but this is secondary.

I can upload an example if necessary.

View 5 Replies View Related

Formula To Get Data Of Specified Period - More Actions For Each Day

Jan 15, 2014

I want know the formula for below issue. I have number of of actions done for each day for a month or more.

If I select a particular period for example 01jan14 to 10jan14 i want to get all actions done in this period.

View 2 Replies View Related

Extract The Word Between Last Backslash And Before The Period?

Mar 14, 2014

I have cells that contain the value :

c: estworking filesabc123.xls
c: estworking filesabc123xyz.xls and so on....

How to write the function to extract the string of text after the last backslash () and before the period (.) (i.e. "123 & xyz in the above example"). The length of the path is inconsistent in the column; still the function should extract it.

View 4 Replies View Related

How To Get The (average Or Sum) In A Limited Period Of Time

Jan 22, 2009

This code help me in typing the date and time in any cell of column (B) automatically just in case I enter somthing in any adjacent cell of column (A)

View 6 Replies View Related

Read Data Only To Current Period

Jan 24, 2010

I'm trying to total data only up to the current period so I presume the answer lies in a =sum and index but I can't get it to work. The attached worksheet explains it better.

View 4 Replies View Related

Find The Earliest Result For The Same Period

Jan 14, 2014

Basically, what I want is to find the earliest EPSI1MN and its associated EPSI1YR and EPSI1NE, within the same period of EPSI1YR and then store the results in a separate sheet. This process is to be repeated for 12 years of data, covering more than 4000 companies.

View 3 Replies View Related

Converting Time Period In Hours

Mar 2, 2006

How do I convert 7.30 hours into 7.21 (ie 7 hours 21min.) Note I do not wish
to use the standard hour:minute formatting.

View 11 Replies View Related

Calculating Time Across A 24 Hour Period

Nov 6, 2007

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.

View 11 Replies View Related

Determine Formula To Know Period Time?

Oct 9, 2011

I am trying to determine a formula to know a period time for example i started to test a unit at 10:20 am of 10/02/2011 and i would like to add 52 hours of test and i would like to know when and at what time will finish the test?

View 3 Replies View Related

Adding Space After Period In If Function?

Apr 24, 2012

I have the following IF function in a spreadsheet. =IF(AE2 = "AZ", "We've recently changed our name from Company A to Company B.", "")

How do you Make it so there is a Space at the end of the period?

View 3 Replies View Related

Restrict Macro To Run N Times During Specified Period

Apr 17, 2013

Is there a way to restrict a macro to run only a specified number of times during a user designated time period, for example run no more than 3 times during a 24 hour period?

We have a macro that runs to alert maintenance personnel if a certain temperature condition is met in machinery. This condition may happen numerous times during a day but we only need to alert the mechanics once a day if the condition is true.

View 2 Replies View Related

Excel Keeps Removing Period From Filename?

Sep 10, 2013

I have some files with filenames like text-text-text-text.lastname.xlsm. When I go to save the file, Excel keeps removing the period between the last "-text" and "lastname". change the structured filenaming convention? So far, I've noticed that this seems to happen with .xlsm file extensions, but not with .xlsx file extensions.

View 5 Replies View Related

Sumif If The Column Is In The Correct Period

Jan 24, 2007

I have a set of costs in column c (more that shown below) that have an indicator - for example 0 or 30 (there are more).

I need to populate rows 18 and 19 with the sumif - i have other tables where it is a straighforward sumif as the periods align, here though I need to check that the periods (rows 4 & 16) are checked and then the right data entered underneath.

I can't find a way to combine the sumif, with an if statement checking that the period is correct too? ...

View 9 Replies View Related

Copyrights 2005-15, All rights reserved