i have a cell which has a time format. but currently its displaying 2.00907E+13. is it possible that i can convert it into the normal time format? for example B2 in my sample is 20090718170156 which actually means 2009/07/18 17hrs 01min(s) 56Sec(s) and after converting, how can i get the time difference between start time and end time?
View 3 RepliesI need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
I believe Range.Numbeformat is the property.
I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.
I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"
The following is not working for me:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim DistractorText As String
Select Case Target.Address
Case "$B$23"
If Target.Value = "Distractor" Then
DistractorText = InputBox("Type of Distractor:")
Target.NumberFormat = "@" & " - " & DistractorText
There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.
I've created a model starting from on another person's workbook, and I'm seeing an issue I haven't seen before.
For instance,
A1 = 10
A2 = 1.5
A3 = A1 * A2 = 15 (all good, so far)
If I change the number of A2 to 0 decimals places, the value shown will be 2 (which is correct, of course), but the value of A3 becomes 20. I would like for this to stay at 15 because the correct value of A2 is 1.5, not the rounded value of 2.
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.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View RelatedI have data in these two formats 7/13/2014 9:24:14 AM and 07/12/2014 13:02. I want them in Am - PM style. I tried date , there is no format which suits my requirement. The first format in excel is general and other is custom under dd/mm/yyyy hh:mm . I have 23k rows, which I cannot afford to change manually.
View 3 Replies View RelatedI've got dates and times in the format of a text string. each string is always formatted in the exact same way to identify the data and time. As such:
Wed Aug 12 16:39:52 BST 2009
Mon Aug 10 14:39:03 BST 2009
Sat Aug 08 18:01:34 BST 2009
And I want to change them to a date formate that excel can understand, so i can search by month, or by date range. The main crux of the question is how to change that string into a excel understandable date though.
If you want to know, this is for my mobile phone bill. I have excel set up to give me a summery of how many texts I've sent, minuets I've used/have remaining. but i want to filter them by month, so I can see how my usage changes.
FYI I put up this question on another forum, however I'm asking it again here b/c I'm not really getting an informative answer there and this project is due soon.
[url]
I'm working on a file right now where dates are in the format "06/01/09" meaning 6 Jan 2009. Or more specifically thats what the data is supposed to mean, however when I change it to word form it appears as June 1 2009. This means dates such as "27/01/09" dont get translated.
How can I fix this? I've tried changing the date format, and I've changed the global settings, yet this isnt working.
In case its relevant, before I pasted this data into the current spreadsheet my global settings were set at month day year, which is what I prefer. But the rest of the office works on DMY so I'm switching to that.
my spreadsheet I have a column(7.65462E+11) it's a upc code.I can format the column to read as a number (value) but when I save it I does not save the number format.It's really interesting that there are some rows that read exactly the same and those rows keep the number formatting.Going nuts.Also when I try to save a date format(2013-mm-dd) It won't save,I've read that it's system default in U.S,is there a way around that ?
View 2 Replies View RelatedHow to translate dates which i already have in the US format, to the UK format.
i.e. mm/dd/yy becomes dd/mm/yy
Please take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.
View 12 Replies View RelatedI have a data set in excel which is the following format which I would like to convert into a text file with a different format. The excel sheet format is as follows:
V C 1 2 3
1 23 0.06 0.04 0.03
1 12 0.09 0.06 0.03
2 5 0.11 0.07 0.05
2 11 0.12 0.08 0.04
The text file should have the above data in the following format with brackets and comma:
(V,C,1)[value]
For example:
(1,23,1)[0.06]
(1,23,2)[0.04]
(1,23,3)[0.03]...........
I have a large chunk of data which I need to convert to a specific format which at first I thought was easy but im struggling to do it! I've tried concatenating and hlookup.
I want to convert this data:
Name
Age
Test 1
Test 2
[Code]....
I want to write down the number (149727273)
To become (1.49727273)
changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
Private Sub Worksheet_Activate()
Dim myDate As Date, mySize As Long, n As Long
With Sheets("Sheet1")
If Not IsDate(. Range("a2").Value) Or Int(Val(.Range("a4").Value)) < 1 Then Exit Sub
myDate = .Range("a2").Value: mySize = Int(.Range("a4").Value)
End With
Currently we do a lot of report crunching using Crystal Reports. We will then dump the data into an Excel sheet to run varying pivot tables or databases off of.
Often we will have a field that the formatting will not match up to anything in Excel. You can change the cell to Text, General etc. The main problem associated with this is running VLOOKUPs. The item we type in will not match up to the data imported from Crystal. We have to F2, the new data before it will match up which is a pain considering there can be anywhere from 30-1000 rows.
I'm trying to solve this problem in 2 ways. First can anyone tell me an easier way to do handle this?
Second, after trying many other things I thought about a macro that would automate the F2 process.
I need to a macro to find any cells with the value 0.00e+00 (which is in scientific format - because thats the format i get from the software download) and change those cells to 00E010 and text format.
View 8 Replies View RelatedWhen I tried to import data in a CSV file, I ran into a couple of issues when trying to change the date format from MM/DD/YYY to other date format. I've already tried a couple things to no avail:
1. Use text to column functions and change the format from text to MMDDY
2. Use format cell function and change the date format
3. Use the text function (text(Cell,"MM-YY"))
Interestingly when I click on the cell and put my cursor and press backspace, the format will change. It seems like there's an extra space, but using the trim function does not work. I can't do this to thousands of data manually.
I have a list with several thousand dates listed like:
19931014
19900724
19910701
19911209
That is, yyyymmdd.
I would like to list it as yymm only. Is there a way to convert it in Excel? I have for a long time been copying and pasting in to Word then doing a search and replace of the first and last two digits, which results in the yymm format I am looking for. A better way would be nice.
I have many rows with dates I want to add a semicolon to. I do not want to type in each one, so I looking for a formula or something that will do this automatically.
I've tried formula, =A1&";" but this changes the format.
3/17/201341350;
In excel if it changes number to exponential format it would default to, for example if number is 12345 -> 1.2345e4, 0.012345 ->1.2345e-2
I was wondering it is possible to which a function to change this default format ie for example 0.012345 -> 12.345e-3
I basically want this kind of formatting as its would display information in terms of milli, micro, pico (or the opposite side kilo, mega, giga and so on).
I am trying to write a macro/program that will save a csv activeworkbook in xls and close it.
The workbook will always be a different name.
The files do not have an extension upon download and formatting.
ie stm0107 (Then they need to be saved as xls. Which is done manually at this time.)
Below are some trial and error attemtps. Thank you for your suggestions.
Sub CloseWorkbook()
Dim Filename As String
'Filename = Workbook.Name
MsgBox (ActiveWorkbook.Name)
Filename = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename = ActiveWorkbook.Name(".xls")
'Filechange = Replace(Filename, "", "xls")
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close Saved = True
End Sub
I would need a macro what would change this format (15 Jun 2009) in cell B2 to 2009.06.15.
View 5 Replies View RelatedThis may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies View RelatedI have a cell which has multiple references including some text and a date. When they are all added together the date comes up with the 6 numbers not a date.
[Code]......
The TR!A11 is the date. Is there code to put in front of it to make it format as a date??
in the date fields excel only recognises some of them as date, so when I try to change the format of dates (mm/dd/yyyy to dd/mm/yyyy) it only changes some of them so I end up with some wrong dates.how I can make excel read them all as date?
View 3 Replies View RelatedI have several graphs in excel which has the percentage number as follows: 15,5 %. I want to convert the number to 15.5 % so it is in line with the english language.
View 1 Replies View RelatedIn any other software, when I save a copy, or export to a different format the original file remains unaffected but in an excel it seems to work differently...and I end up having to save my file back to the xslx format. this extra time wasted when I am regularly exporting but it also makes me a bit nervous that I might accidentally not save back to the format with all my worksheets etc etc..
View 1 Replies View Related