Join Cells & Retain Date Formatting

Nov 30, 2007

I have a number in A1, another number in B1, a Date in C1. In D1, I am referencing all 3 of the other cells: =CellA1 & "-" & CellB1 & "----" & CellC1. Trouble is the date is coming through as a number. Is there a way that I can get the date to show as displayed as opposed to the value of the cell?

View 2 Replies


ADVERTISEMENT

Join Time & Date & Retain Formatting

Jan 29, 2008

I have written some code which takes data from multiple spreasheets and formats it in a specif way. However one problem I have is getting the right data format. As you will see from the input files the date and time cells are separated into two and the macro is supposed to aggregate these together in one cell with a space in between - but I cannot manage to perfect this !!! The output is in a number format instead. I have attached the input files. The code written so far is as follows - works perfectly apart from date format that comes is in the output file once you run the code.

Dim Obj1 As Object
Dim Obj2 As Object
Dim UserList()
Sub RunMe()
Dim Str1 As String
Dim myPath As String
Dim i As Integer
Set Obj1 = CreateObject("excel.application")
myPath = "C:Test" 'please note you should a "" at the end.
Str1 = Dir(myPath & "*.xls")
Set Obj2 = CreateObject("excel.application")
Do
Obj1.Application.DisplayAlerts = False
Obj1.Workbooks.Open (myPath & Str1)
'For i = 1 To UBound(userslist).............

View 6 Replies View Related

Join Cell Text & Retain Format Of Each

Oct 16, 2006

I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".

View 4 Replies View Related

Join 2 Times In Cell & Retain Time Format

Aug 23, 2007

I'm usually able to find my answer but I couldn't find anything to match what I need. Anyway... this is either really simple or impossible to do. I have two cells which have times in each. Say A1 has 5:00 PM and A2 has 10:00 PM. I'm just trying to make it so one cell will say:

From 5:00 PM to 10:00 PM... So I tried this ="From " & A1 & " to " & A2

But as I'm sure you already know that yields this result:

From 0.708333333333335 to 0.916666666666668

View 3 Replies View Related

Join Numeric Cells To Make Date & Time

Jan 2, 2007

I am using this formula to combine Excel Dates & Times which is in seperate cells.
day Month Year Time

=B13&"/"&C13&"/"&D13&F13
and trying to convert to Numerical Time for further calculations.
I have tried many Format variations but nothing seems to work.

It will show the correct Numeric Time if the Formula: =NOW() is used in Cell K12
But it won't show the same from the imported data that is Text format
in Cell I 12

The sample W. Book shows a better explanation of the problem.
The end result is to show how much time has lapsed in Cell M 14 by converting Time to Numerical and simply do the sums. Works otherwise but not in this instance.
The reason to trigger various macros according to pre-set Time Limits Etc.

View 9 Replies View Related

Join Text & Number & Retain Number Format

Jan 11, 2007

I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.

View 8 Replies View Related

Retain Formatting During Concatenation

Feb 4, 2009

I'm concatenating data in 5 cells (Author, Year, Title, City, Publisher), one of which is in italics (Title). When using concatenate (or &) the formatting is removed, and I need to be able to retain that formatting.

Example:
Col B
Aaron, M. (ed)
1999
The Body's Perilous Pleasures.
Endinburgh
Edinburgh University Press

View 4 Replies View Related

Retain Formatting In Textbox?

Mar 1, 2013

I have a textbox that displays text from another cell in the workbook. The other text has some sections in bold, but when it displays in the textbox it doesn't show the bold text - The code I'm using at the moment is below.

Private Sub UserForm_Initialize()
Dim R As Long
ComboBox2.List = Array("QuestionSet1", "QuestionSet2")

[Code].....

View 3 Replies View Related

How Can I RETAIN Formatting In A Pivot Table

Mar 21, 2007

My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.

I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.

I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?

I'm using Excel 2003 on Windows 2000 Professional.

View 9 Replies View Related

Retain Formatting When Inserting Rows Via VBA?

May 21, 2014

I'm using the following code to insert rows in a table of data:

Code:
For i = LR To 2 Step -1
If Range("D" & i).Value Range("D" & i - 1).Value Then Range("A" & i).EntireRow.Insert
Next i

It works as expected, but as my data table is formatted, the last row inserted does not contain the same formatting as the table.

View 1 Replies View Related

Retain Calculated Field Number Formatting

Aug 16, 2006

I use pivot tables extensively in my job. I have several spreadsheets that feed from acess database tables to build pivot tables. Besides the fields imported from Acess, I also create calculated fields. The problem is, for both the calculated fields and the imported fields, the number formatting does not stick. I know how to go to field settings, then to number, and change the customized format. The problem is, once I take that field out of the pivot table and bring another one with a different format (say, bring a field with currency format and take away one field with a percentage format) and then bring the original field back to the privot table, the number format is gone.

This is really annoying since I work with dozens of fields, and it takes time to change the number settings every time I change fields on the pivot table, and the number of decimal places, etc. I stress that I know how to change the number formatting on fields, it's just that it excel does not remember it afterwards.

I thought that maybe the formulas in excel might allow one to include the formating, but I have not been able to find if this is possible, and if so, how to do it. So, for example, the following formula for a calculated field:

=IF(ISERROR(Contribution/'Block Hours'), "0", (Contribution/'Block Hours'))

would be nice if the formula itself could include the number formating. I tried including something like , 0.00% at the end, but it does not work.

View 4 Replies View Related

Paste Number From EXCEL To WORD And Retain Formatting

Aug 6, 2014

I am importing a data table from Excel into word. All works well except for the first column of my data, which contains numbers formatted as currency. I am using a bunch of loops to transfer the data into word, and for each cell the code looks like this:

[Code]....

Is there a way to tweak the code and preserve the formatting when I am importing numbers into word?

I tried the following code but it gives me type mismatch error:

[Code] ....

View 1 Replies View Related

Copy Chart Without Source Data & Retain Formatting

Feb 21, 2008

I have 2 sheets in one workbook; one sheet has a chart with a data table, the other contains the source data for the chart. I want to copy and move the sheet with the chart to a new workbook, but without the source data. I break all links, but then the chart's data table takes the default values and formats in the new workbook after I break the links. For examples, the source data has formats such as currency and no decimal places. However, after copying the sheet, the data table reverts to no dollar sign and decimal places. In addition, the months on the x-axis turn to numbers when the links are broken.

View 3 Replies View Related

Remove Conditional Formatting Conditions, But Retain Formats

Jul 2, 2008

I am using excel 2007 - I need the extra available columns in Excel 2007 for my application.

I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.

Format painter and "paste special - format" copies any formats as well as the conditional format rules.

Is htere a method within Excel to do this or can someone suggest a workaround?

View 3 Replies View Related

Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?

Sep 16, 2013

A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.

dust 1.xlsx

View 2 Replies View Related

Retain Date Format For Input Entered As Date?

Mar 24, 2014

I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.

The formula works fine for all except for date format input.

Sample:

N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09
12335--->12335
12/3/1923---->58936 (I want the date to be retained)

why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.

View 4 Replies View Related

Combine Text & Date. Retain Date Format

Nov 8, 2006

Date:31-Oct-06 (the date is in cell A3)

Formula: "as at"& " "&A3

Result: as at 39021

I need the results to be "as at 31-Oct-06". Is there a formula for this??

View 2 Replies View Related

Join Text With Current Date & Format Date

May 8, 2008

Is 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

View 4 Replies View Related

Set And Retain The Date That A Value Is Reached

Dec 10, 2009

I have a quantity column on an inventory sheet which automatically updates the available quantity based on sales from a sales sheet. But I need to know the date the quantity reached zero.

That date would always be "today," on the day the quantity = 0, but how do I make the date NOT update to "todays date" on the next day, and the next, etc. It needs to stay as the date that 0 qty was reached.

View 9 Replies View Related

Subtact X Days From Date Variable & Retain Leading Zero

Jan 12, 2008

I am trying to insert a leading zero for single digits. A3 contains a date, i.e. 01/10/08


d = Range("A3")
dDay = Format(d, "dd")
'The result is '10'

dPrevDay = dDay - 1
'The result is '9'

dPrevDay = Format(dDay - 1, "dd")
'I anticipated the result of this to be '09', but it is '08'

How do I get the result of '09'?

View 7 Replies View Related

Conditional Formatting Of Blank Cells And Date Cells?

Jan 17, 2013

I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.

conditional formatting.xlsx

View 4 Replies View Related

Unmerge But Retain Value In All Cells

Aug 10, 2009

Seems like a dumb question but is it possible to unmerge say one "3 cell" merged cell with a value of "A" into 3 SINGLE CELLS with a value of "A"?

View 9 Replies View Related

Join Date And Time String And Keep Cell Appearance Same

Aug 23, 2007

I am joining two cells together, date and time.
I1 & J1
12/13/2005 12:35:00 PM

I want it to appear as
12/13/2005 12:35:00 PM

but after joining i get the serial value of:
38699 0.524305555554747

View 9 Replies View Related

Date Formatting Cells

Oct 13, 2008

Is there a way to date format a cell so that when I enter only the number date (ie today is October 13 so I'd enter 13) the cell formats to the current month with the date?

So for today I'd enter "13" and the cell would read 13-Oct. Additionally, when the month turns to November that same cell, without me making any changes to it, would change to 13-Nov. For future dates it would always show the current month with the date I entered manually.

View 3 Replies View Related

Join 2 Cells In VBA

Dec 6, 2007

I'm writing an Excel Macro and want to sum a number cell with the value "45667" and a text cell with the value "Prague" and return it to a new cell with the value " 45667 Prague"

I've done it successfully with the two text cells "John" and "Hopkins" to "John Hopkins".

This is my

Sub SumCells()

Dim FirstName, SecondName, Zipcode, City, fullname, fulladdres, space

FirstName = range("cell1").value
SecondName = range("cell2").value
Zipcode = range("cell3").value
City = range("cell4").value
space = " "

fullname = firstname + space + secondname
fulladdress = zipcode + space + city

The problem is that city is recognized as "Prague", and the zipcode as 45667 (without the "")

View 3 Replies View Related

Retain Leading Zeros In Calculated Cells

Sep 15, 2014

I had been trying to calculate cells that contain numbers with leading zeros.

I can't convert the cells to TEXT because Formulas don't work in TEXT Cells.

I cant use Custom Formatting because the rows may contain different number of leading zeros.

Also Is there a way that I can trim the numbers from Col A and put them in C?

Currently I don't like having to change the RIGHT(G2, 3) to RIGHT(G2, 4)

if the number is smaller than the number of char. option.

View 9 Replies View Related

Using Different Cells Date For Conditional Formatting?

Jul 31, 2012

I feel like this is a pretty simple question to answer but I cant find an answer to it anywhere or I'm just not skilled enough to understand it. I have two columns D and E that have dates in them. Column D is the purchase date of a computer and column E is how many years months and days its been since it was purchased. I want both columns to be the same color. If the computer is 2 years old or less I want it green, 3-4 yrs old yellow and 4+ red. I have it working for column D but I can't figure out how to apply it to column E.

View 7 Replies View Related

Formatting Cells To Date - Best Practice

Aug 7, 2006

I thought I read somewhere that it wasn't good practice to format a whole column(s) b/c then you would have empty cells that were formatted. Is there a better way that you guys format your columns without selecting the whole column? I know you can do this in VBA, but I was just wondering if there was a way that I'm unaware of.

Second, is there a formula that can inserted into conditional formatting, that will check a named range against two other cell values. If that ranges date is > AB2 and <AC2 then it will bold that range? I'm trying to figure out how to do things without always relying on VBA if possible.

View 2 Replies View Related

Join Cells & Compare To Single Cells

Jul 5, 2007

i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. I’ve Highlighted in ROW 947 one that should Say NO yet Says YES.

View 3 Replies View Related

How To Join Cells In Different Columns

May 31, 2012

I have a list containing hundreds of rows and I need to join the cells in different columns.

Eg. Col A, Row 1 is "Red Maple"
Col B, Row 1 is "Acer Rubrum"
Col C, Row 1 is "60mm"

I need to join the row into one cell reading "Red Maple Acer Rubrum 60mm"

View 2 Replies View Related







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