Dates - Single And Multiple

Aug 19, 2009

I'm doing our training schedule for 2010. Some course we have are multiple date (ie. September 12 - 13) some are singular (ie. January 9). We have a date column. For the single dates it automatically converts to date format. For the multiple date is stays as text format. this is very confusing when we sort by date as every course that has multiple dates doesn't sort properly (because it's in text format). Is there a practical way to go about doing this, so if someone were to sort by date it would would sort properly -whether it's multiple or single dates? Let me know if anyone has any ideas. I'd prefer not to have 2 columns - column 1 being (course start) and column 2 being (course end)..but not sure if this is possible.

View 5 Replies


ADVERTISEMENT

Split Multiple Dates Stored In Single Cell

Jun 14, 2008

how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?

View 5 Replies View Related

Find Multiple Instances Of Single Criterion In Row & Return To A Single Col

May 8, 2006

Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.

I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.

I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.

NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.

Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0

Scenario:
Looking for Numeric Criterion 1 (one).

Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1

In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.

View 14 Replies View Related

Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format

Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies View Related

Counting Consecutive Dates As A Single Occasion

Jan 8, 2010

I'm trying to do is to get Excel to look at the dates and, if they are consecutive (Fri - Mon inclusive is also counted as consecutive) I need Excel to tell me that this is one occasion. It then needs to count each seperate occasion.

I've attached an example with each different occasion colour coded. In the example there a five occasions. Thus the Bradford Factor would be 187.5 (5*5*7.5).

View 2 Replies View Related

Making Single Graph With Two Datasets That Have Individual Dates

Apr 3, 2014

I want to make a single graph from two data sets that have individual dates.

help!.xlsx

E.g.
Data set 1
1. January 2010 2. january 2010 ... 10. december 2010 etc
10 15 ... 10 etc

Data set 2
1. January 2010 ... 1. January 2015 etc
200 ... 200 etc

View 8 Replies View Related

Count Of Consecutive Dates As Single Occasion Per Patient

Jun 2, 2014

I couldn't figure out how to change this function to allow for weekends.

Function to count consecutive dates as one occasion
=--((NETWORKDAYS(SUM(c1)*(B1=B2),c2)-1)>1)
Column A Column B Column C Column D ( I would then use this column in a pivot table to calculate)
pat name pat id date of service # Admissions

[code]....

View 4 Replies View Related

Replace Many Dates With Single Date Based On Condition

Dec 28, 2011

I have a column of over 20,000 rows, showing employee hire dates. For the purposes of a specific calculation, I want to replace all the hire dates that are prior to 1/1/2011 with 1/1/2011. Is there a simple way to do this all at once with a Replace statement in my code without having to look at each record individually?

I know how to replace one specific date with another all at once, but I don't know to do it when I'm looking for more than one specific date.

View 6 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:

TABLE 1:

Project
Benefit Type
Delivered or Enabled
Benefit

PJ1
Financial
Delivered
Saving of $4M over 24 months.

[code]....

I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.

TABLE 2:

Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled

[code]....

So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

VLookup - Single Value Lookup Returning Multiple Records Into Multiple Columns

Feb 7, 2014

Certification and Training tracking.xlsx

I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.

What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.

View 6 Replies View Related

Splitting Multiple Entries In Single Cell Into Multiple Columns

Jan 15, 2013

I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows

Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID

Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3

How I could achieve this as I have a number of projects where this would become useful

I know you can use delimiters but with spaces between the values I just can't fathom a way forward.

View 12 Replies View Related

Tracking Multiple Dates - 5 Columns - 5 Different Dates

Feb 14, 2014

I have 5 columns with different dates to track when certain items expire per employee. Conditional Formatting is working like a champ but the data is growing.

I'd like a way to 'quick glance' at all my people that are expired, coming up on expired (this month and next).

I was thinking if there was a script to run through all 5 columns, if the date was less than today, throw the 1st,2nd and 5-9 column (in that row) into a new table. That way i can keep my big sheet but have another sheet or table that i can see QUICKLY who all needs to get their stuff up to date.

View 1 Replies View Related

Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)

Jan 7, 2009

If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H

View 9 Replies View Related

Single Value In Multiple Value

Nov 22, 2007

This is the third time I am posting my problem. Please help in by solving my proble. I want to convert single value in a cell in multiple value in the same cell. I also want to define a delimiter in the same cell.

Also don't want to use split function as it creates different cells.

For Example: "34,654, 5667, 566" is a value in the same cell. Excel takes it as a single value, how do I make excel to take it as multiple value in the same cell.

View 9 Replies View Related

Days Between Two Dates Separated Into Days Per Month For Multiple Dates - Excel

Feb 13, 2013

I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.

The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.

Input Data
Result
Start Date

[Code]....

View 3 Replies View Related

Multiple Rows To A Single Row

Nov 28, 2009

I have a long spreadsheet, that has a format something like this: ...

View 6 Replies View Related

Single Row To Multiple Row Without Skipping?

Dec 18, 2013

I have a dataset where I have 1 row for one person with different information, I would like to turn this into multiple rows eg,

Henry 37 Likes Peanuts Brother George 24 likes tomatoes sister Sarah 21 likes olives

into

Henry 37 Likes peanuts

View 6 Replies View Related

Single Row Into Multiple Rows

Dec 24, 2007

Is there a way convert single cell with wrap text into multiple cells with the number of resulting rows based on the number of lines in the original cell. Due to the restrictions of my overall printable "document", I must use multiple cells of the same height. Any change of row height would throw off the desired final result.

As an example, when I start with my original cell in text wrap and set column width I get something like the following in cell F2:

ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME
MATERIAL)

This looks great, but it is all in one cell, and the four lines of wrapped text have obviously increased the row height which is bad.

I have tried using the LEFT and MID functions to force the text into different rows based on character length, but this is sort of messy since I am never sure what the input will be resulting in something like the following (this time in cells F2:F5) with spaces at the beginning of a line or breaks in the middle of words:

ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME MATE
RIAL)

My ultimate goal would be to somehow format the original input data text so that regardless of the input, the text would be restricted to a certain column width, sending the remaining text to the next consecutive rows down, and maintain the integrity of the original. I can have as many intermediate cells, formatting as I go to get to this point.

Of course, I could manually enter the text in the different rows every time, but it would be nice to have a succession of formatted cells so that the desired end result would be automatic. Hopefully, the answer does not involve too much in the way of macro or any VBA code since I am a pitiful novice, and we are sort of under pressure to get this project under way.

View 9 Replies View Related

Sum Multiple Or Single Rows

Feb 9, 2007

I have a cash receipt worksheet and want to sum up the days cash. My problem is some days there are one receipt and some there are five. I have to keep hitting the sum button to make it include the proper range.

View 7 Replies View Related

Match Multiple Values With Multiple Values From Another Table - Return Single Value

May 12, 2014

I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...

Attached worksheet : Test booklet.xlsx‎

View 4 Replies View Related

Multiple Lookups In Single Cell

Jun 13, 2014

I am looking for creating a dynamic meeting log/calendar for my team. There's a couple of elements to my questions below:

I have a log with columns such as "Meeting Date", "Client", "Attendee" and have also set up month calendars on separate tabs which automatically update the dates according to a "Year" toggle using Janoffset, Feboffset, etc.

What I would like to do is have these calendars automatically populate a cell beneath each date with any meetings on that day. The client name and then a space then the attendee in brackets would be sufficient and with each meeting having its own line in the cell).

I have gotten a Vlookup working but have run into two snags: 1) where there is more than one meeting on a single day 2) I can only return one piece of information in the cell or otherwise have it blank, I can't have the client name AND attendee.

Is there any way to lookup all of that information in the one cell or is this just a pipe dream?

I have attached my workbook which contains my workings so far and in the first cell of Jan shown the "ideal" format. I'd like the data in.

View 14 Replies View Related

If Then Multiple Arguments For Single Cell

Nov 14, 2008

IM getting a too many arguments error on this ...

View 8 Replies View Related

Add 1 Row From Multiple Files Into A Single Spreadsheet

Dec 3, 2012

I have a group of files on a network drive.

Directory is S:RailserveAvailability

The Excel files all start with Availability. After Availability is the date the file was created, followed by .xlsx So example, Availability041012.xlsx

The file has multiple worksheets, but work sheet names are the same in each file. I need data from the "Car Summary By Product Line"

The data from each sheet that I would like to get from each sheet starts in A4 through I4. There are forumlas in several of the cells so would need to be a Paste Special Values type copy.

So code/macro would do this:

Copy row A4:I4 from File Availability041012.xlsx, Worksheet "Car Summary By Product Line" to a row in the Master file

Then repeat for the next file Availability041212.xlsx (this data isn't typically generated on weekends so won't be a consistent date + 1) copied to the next row down on the Master file. This would allow me to chart data for car counts from day to day. It would be really cool to have the file date in Column J so I could chart by date and show a trend, but I know beggers can't be choosers.

I have roughly 200 days with multipule product lines so copying and pasting each one wouldn't be feesible.

View 12 Replies View Related

Combing Multiple Spreadsheets Into Single One

Dec 18, 2012

I need to combine these 6 spreadsheets into one single spreadsheet. It won't seem to allow me to paste it.

It needs to begin with 2012 and count down.

2011.xls2010.xls2009.xls2008.xls2007.xls

View 1 Replies View Related

Convert Multiple Rows To Single Row

Feb 4, 2014

I have a database of addresses that has each address as several rows which I want to convert into a single row. In the source file there is a blank row between records. I have attached a sample file with two sheets in it. One is the "before" data format and the other sheet is the "after" example of how I would like it to come out. We have thousands of records to convert so I need something that can be automated instead of a massive cut and paste operation.

Here is the sample: sample conversion.xlsx

View 4 Replies View Related

VBA To Refresh Multiple Rather Than Single Sheet

Jun 27, 2014

The code below enables entire workbook protect/unprotect, however ultimately it refreshes a particular sheet (sheet 8 in this instance) when the relevant data table is edited.

I have 7 sheets (one is the data table and the other 6 house pivots that work off this data table). What I want to happen is for the code to enable the unprotect, refresh and protect of all the sheets exactly like it does for the current sheet 8 (I am simply new to this an am unsure of how to write the code to make the refresh happen for all sheets).

[Code] .....

I have inserted the following code in the 'ThisWorkbook' section to allow for to sheet protection macro to work:

[Code] ......

Along with the following code in the sheet 8 section to enable the refresh macro to work:

[Code] .....

View 5 Replies View Related

Multiple Outputs For Single Lookup Value

Feb 4, 2014

On one of the reports which i am doing to identify the series of values for a single input data. In the attached spreadsheet there are 2 tables, table 1 has complete data with column a is the lookup value and column b is output value

I have to fill the table 2 in the same manner as shown in the attached template. there could be many different for a single lookup value but i am targeting to look for the 1st 9 matches and last matched value. eg. if i am looking for the value 9 which has 15 different values i want to display the 1st 9 matches (p,q,r,s,t,u,v,w,x and ad - being the 15th match)

Not sure whether this could be possible by using formulas?

View 5 Replies View Related

Transpose Multiple Columns For Single Row

Feb 12, 2014

Before
XYZ1
XYZ2
XYZ3
XYZ4
XYZ5
XYZ6
XYZ7

After
XXXXXXXYYYYYYYZZZZZZZ1234567

[Code] .....

View 10 Replies View Related

Protect Single Or Multiple Cells

Mar 12, 2014

How to protect a single or multiple cell in excel.

View 1 Replies View Related

Multiple Conditions To Return Single Value

Dec 22, 2011

if color(white),pattern(6" tile),finish(gloss),width(>36") & height(>98")then return item ordering# different worksheet datadase.

WALL SELECTIONColor NamePatternFinishExisting
Wall Ht.Wall System stops at:
Wall Repair[SELECT COLOR][SELECT PATTERN][FINISH][SELECT HEIGHT][SELECT HEIGHT][REPAIR]Plum
Wall SpecsSoap Dish Wall SpecsBack Wall SpecsExtra
Wall Specs (if needed)Finished Size:[WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT]
Quanity:[QTY][QTY][QTY][QTY]Item Ordering #:

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved