I get data in csv format from an external source.
The problem is that it gives me dates in a lousy format of ddmmyy
For example 120706 or 050606.
Excel doesn't recognise them as dates and just displays them as numbers (120706 or 50606)
I want to see them as dates, so I select the column and change the format to date
BUT
excel sees my "numbers" as a julian date, and jumps me forward to the dates 24/06/2230 and 20/07/2038 respectively.
I have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.
Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
I need to convert data in HH:MM:SS format to normal numbers
for example if I have a time ( 10 hours 30 mins 30 sec )
10:30:30 then it should reflect as 10.3
I basically am not looking to convert the data to complicated hours format like say 10.9 and all as it complicates the other calculation.
How to use data validation to restrict the input format into a cell. I'm trying to limit a cell to the following format: either ###/###' or ##/##'. NOTE: the apostrophe at the end. Here is what I've been trying to get the thing to work for the 2nd case:
=AND(ISNUMBER(LEFT(H1,2)+0),MID(H1,3,1)="/",ISNUMBER(RIGHT(H1,2)+0),MID(H1,3,1)="'")
But I'm getting an error when I input a test into the cell, e.g. 12/45'.
I thought I had this solved but an inconsistency has shown up. I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
I have a long list of chemical formulas that I want to format (partially) as subscript.
Basically what I need the macro to do is look at each character within a cell and check to see if it is numeric. If it is AND it follows a non-numeric character it should be formatted as subscript.
Examples
H2O the 2 should be subscript
H2SO4 the 2 and the 4 should be subscript
2CCl4 only the 4 should be subscript
CuSO4 - 5H2O
I want to record the amount of people attending a venue over a given period. The information presents names and did they attend. In column "D' from D5 to D45 the information will be yes or no. I want a formula that calculates the amount of YES and shows the answer in a numeric format
View 4 Replies View RelatedI have several numeric values in one cell enetered using the alt return method, can these values be recognised as individual entries and therefore when the cell is formatted as $ it is applied on all entries?
View 2 Replies View RelatedI have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st!
The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error!
Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
One system I use provides an output of the previous day the system ran (which is the date I need) but provides it in a strange format. Today, for example, would appear in 4 different cells.
Day Month Century Year
28 12 20 12
I would like to take this output and change it to 28-Dec-12.
I tried bringing them together using the code:
[Code] ......
Hoping (but but pretty sure it wouldnt work) that Excel would read it as 12/28/12 and convert it to 28-Dec-12 but that did not happen.
Is there any way to do this without using VBA?
If not, is there an easy way to pull this info using a very simple macro?
I export data to Excel and now I have date like
05-08-14
06-08-14
70-08-14
If I change the Date format to AUG-14 and than pivot it than its coming in tree column which I need only 1 for whole month
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
I have some dates in Excel in different formats and I need to convert them all to a uniform date format of MM/DD/YYYY.
The data is in this format:
82012
8152012
52012
5152012
The days of the month don't really matter. It's the month and year that I need in a date format.
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I have been trying to create a userform that will generate a numerical list starting in a given cell using a starting number, an ending number, and an increment that the user will imput into a textbox. I have a userform set up, but I do not know how to do any coding. I have very little experience with excel.
View 2 Replies View RelatedI 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.
I have created a userform but I am having extensive problems with the date formats.
My system is set to UK and short date is set to: DD/MM/YYYY
When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.
So I finally figured out to use DateValue to format it correctly for example: ...
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 RelatedEvery month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range
I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter
The date column is E:E.
The store number column is G:G
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies View RelatedI have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
12/10/2013
12/9/2013
9/11/2013
9/9/2013
7/25/2013
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
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 RelatedExample......
In D2 I have: =B2
In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
How we can convert any data which is in General format in to date format.For ex I've:
19/03/2012 14:57 (General format)
I want the output/result
19-Mar