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


ADVERTISEMENT

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

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 View Related

Count IF And IF Then Subtract Two Times In 24 Hr Format To Recieve Time On Task

Jul 9, 2008

I get a spreadsheet downloaded to excel with a variable of 15 names.

I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500.

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

Copy A/F Data To New Sheet & Retain Format

Jan 8, 2010

Using xl 2007 - The following code is working fine. except on completion my new sheet does not have the same nice formatting of the Original Sheet.. So I need to Incorporate the formatting of the new sheet to be the same as the Original Sheet.

Sub DumpAutoFilterToNewSheet1()
Dim wss As Worksheet
Dim wsd As Worksheet
Set wss = ActiveSheet
If ActiveSheet.FilterMode Then
Set wsd = Worksheets.Add
wss.AutoFilter.Range.Copy _
Destination:=wsd.Cells(1, 1)
Else
MsgBox "Nothing is currently Filtered" & vbNewLine _
& "Filter a Column and Try Again."
End If
End Sub

View 9 Replies View Related

Retain Format Importing Text File

Jun 6, 2007

I am having problems choosing a format for the column in my spreadsheet that contains my account codes. This is especially a problem when the code starts with an '=' sign and is followed by a number or has a '£' sign followed by a number.

I receive the file in a .txt format which I guess if I get it that way I would not have a problem but as I need to work on the file in excel before uploading it I experience problems. For instance when I open the file the codes that begin with '=' + a letter for instance =Y just comes up as '# name?' and only when I click in the cell can I see that it is =Y. Also the code '=1' just comes up as '1' as does '£1' - now this causes problems as for a start I need to do Vlookups on the account code but now instead of three distinct codes I have 3 1's.

The only way that I have discovered how to solve this is to click into each cell and add a ' before the text. (this changes the format to text - right??) But obviously this is very time consuming - is their a quicker way to change these cells. I can't just right click and format cells - text. This does not work?

View 5 Replies View Related

Join Text & Format Part

May 15, 2007

I am trying to reference text and another document. The text I want is " stock option expense negatively impacted 1Q:07 by:" and the number i want it to bring back is currently in the model as a percentage. However I would like to bring it back in basis points which would be the percentage times 10,000. So if its in the model as 0.4%, I'd like to bring it back as 40 bps. Here is the formula I am using that is insufficient.

="Stock option expense negatively impacted 1Q:07 by: " & TEXT([FOSL2.xls]QTR!$BK$34,"0") & " bps."

View 6 Replies View Related

Time Difference Formula Where Both Times In 1 Cell

Aug 14, 2007

I need some IF formula I believe that will yield an answer between 1 - 5. I'm not swavey enough with these things to figure this one out... trust me I tried and it keeps getting more confusing for me.

If the time worked is between certain time criteria then it would equal 1 - 5 depending on the time.

Example: If I work between the hours of 5am and 1pm then I would be in the Open/Mid range and would need to equal 2. If I only worked a few hours and my hours fell only between the Mid range then it would equal 3.

Then based on that... It would automatically fill in on the deployment charts... My name would show up on the Open and Mid Deployments under the task chosen for me to do that day.

I've attached a small sample of what I am looking for to kind of help show what I need. The highlighted areas are the areas I'm not sure how to do.

View 9 Replies View Related

Split Date & Time Cell & Format Time As Hundreth Of Second

Sep 6, 2006

I have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.

Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971

View 6 Replies View Related

Format Date & Time Cell To Hide Time If 0:00

Dec 28, 2006

I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?

View 6 Replies View Related

Calculate If Time Value In Cell Is Between Times Located In Two Other Cells

Feb 7, 2008

I'm trying to calculate whether a time value located in a cell is between the times in two other cells. Here is what I tried:

=IF((AND($A62>=C$55,$A62

View 9 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

Converting Times Stored In Date Format To Number Format?

Mar 31, 2014

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.

View 5 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

Format Cell For Date And Time

Apr 13, 2009

I have a problem when i try to format a cell. i download data from a website and it gives me the date and time in the first column, ex. 1/1/2009 5:00:00 pm. when i format the cell to date, the time is erased and the date remains which is what i want, ex. 1/1/2009. however, when i click on that cell or any other in that column, the display above, or "insert function" line still show the contents of the cell with the date and the time, ex. 1/1/2009 5:00:00 pm. is there any way i can change that so the time is erased and only the date remains, ex. 1/1/2009 when i click on the cell?

View 9 Replies View Related

Format Time From Cell In VBA Code

Apr 1, 2009

I am trying to insert data from an excel sheet to a sql database. Everything works okay but the Time value. In the Excel sheet it is '12:09 PM' and when it is read with 'cl. Offset(0, 6).Value' it turns it into '0.50625'. Is there a way to format it so it can be inserted as a datetime?

Dim cl As Range
For Each cl In Range("A1:A12")
stSql = "INSERT INTO GasData (Delivery,OpenPrice,High,Low,Last,Change,Time) VALUES ('" & _
cl.Value & "','" & _
cl.Offset(0, 1).Value & "','" & _
cl.Offset(0, 2).Value & "','" & _
cl.Offset(0, 3).Value & "','" & _
cl.Offset(0, 4).Value & "','" & _
cl.Offset(0, 5).Value & "','" & _
cl.Offset(0, 6).Value & "')"
cn.Execute stSql
Next

View 2 Replies View Related

Pull Date & Times Into Seperate Cells From Date & Time Cell

Apr 2, 2008

DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?

View 4 Replies View Related

Time Stamp To Text - Not Using Cell Format?

Feb 26, 2014

I have a large number of cells with time stamps: 00:07:45 (hh:mm:ss)

What I would like to do is just show the mm - but NOT format the cell as [mm], just as a general / number '7'.......

View 3 Replies View Related

Work Out Time In And Out Based On Cell Format

Jan 14, 2009

I have a sheet that I want to calculate what time a shift starts based on a cell shading.

Basically Cells C6 and D6 need to be auto calculated based on the cells that are shaded from G6:L6

Look at the Attachment and you will see what I mean.

View 8 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

Formula Works On Numbers But Not On Time With Format Cell?

Jul 27, 2014

The formula works on numbers but not on time with format cell : [u]:mm:ss

HTML Code: 
8
7
6
11
12

HTML Code: 
8:00:00
8:00:00
6:00:00
3:00:00

[Code]....

View 1 Replies View Related

Macro / Formula / VBA To Change Time Format And Data In A Cell?

Mar 7, 2014

I have a time format in one excel sheet, I need a macro or a formula to change it into a specific format, this is to be able to import it to another software.

I have attached a file here you can see an example, this I manually, I want Row D and E into the format in A, I s it possible to make it in a macro or formula, manually it takes a long time.

File Type: csv TimeFormat.csv‎

View 11 Replies View Related

Input And Display Data In Cell In H:mm Format Without Changing To Time

Feb 25, 2010

Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.

When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.

Example:

B20= 1:15 (1h15m) NOT 1:15:00 AM

B24= :30 (30min)

B28= :45 (45min)

B32= 1:45 (1h45m) NOT 1:45:00 AM

B38= 4:15 (4h15min)

I have attached a copy of the form for better clarification.

It doesnt seem to be complicated, but it's certainly a lot more than I thought.

View 4 Replies View Related

Date Format: Extract Only Time And Paste It To Cell A56 In Same Sheet

May 6, 2006

I get one report.xls workbook by FTP. This book contain date and time in cell A14 in sheet1. e.g. 30-04-2006 19:38:00

I need to have macro to extract only time and paste it to cell A56 in same sheet. How can I do that. The date format is 30-04-2006 19:38:00 and it may be 05/05/2006 7:15 am. Or it could be any type of date format. Please send me how can I work with different date and time format to extract the only time and paste to other cell.

View 5 Replies View Related

Retain Formula In Cell

Feb 4, 2009

Is it possible to retain the formula in particular cells within an excel spreadsheet whilst clearing the rest of the sheet, and if so how?

View 3 Replies View Related

Average Of Times Which Are In Dd:hh:mm Format

Apr 3, 2008

How can you find the average of times?

I have times like
00:01:00
00:00:15
00:00:08
All are in dd:hh:mm format
I need to find their average which shld be 00:00:28 (approx 27.6 mins)
When I do average(A1:C1) its giving me div/0 error.

View 11 Replies View Related







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