Date Format From Numeric

May 14, 2009

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.

View 14 Replies


ADVERTISEMENT

Excel 2010 :: How To Convert Numeric Dates Into Proper Date Format

Aug 21, 2013

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?

View 2 Replies View Related

Numeric Format Based On Cell Value

Nov 17, 2009

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.

View 4 Replies View Related

Converting Hh:mm:ss To Simple Numeric Format?

Oct 9, 2011

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.

View 9 Replies View Related

Using Data Validation For Numeric Format

Dec 17, 2012

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

View 9 Replies View Related

Format Some Numeric Characters In Cell As Subscript

Feb 16, 2010

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

View 2 Replies View Related

Format Specified Numeric Characters In Cell As Subscript

Feb 16, 2010

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

View 2 Replies View Related

Recording Alpha Data In Numeric Format?

Feb 12, 2012

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 Related

Multiple Numeric Values In One Cell To Format As Dollars

Nov 28, 2013

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

Pass Number From TextBox To Cell In Numeric Format

Aug 24, 2007

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

View 6 Replies View Related

Numeric Date Into Alphanumeric Date

Dec 28, 2012

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?

View 3 Replies View Related

Date From Numeric To Alphabet?

Aug 5, 2014

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

View 4 Replies View Related

Converting A String To A Numeric Date

Jul 13, 2009

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?

View 9 Replies View Related

Convert Numeric Text To Date

Sep 6, 2012

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.

View 2 Replies View Related

Excel 2007 :: Date Filtering - Change Date Format Supplied By Date Picker

Apr 24, 2012

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?

View 7 Replies View Related

Create Numeric & Date Series Lists

Sep 27, 2007

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

Userform Date Format Reverting Back To Us Format On Change Event

Aug 10, 2009

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

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

Macro Or VBA - Search General Format Convert To Date Format

Oct 26, 2012

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

View 2 Replies View Related

Excel 2010 :: Number Format Keeps Switching To Date Format?

Aug 1, 2013

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.

View 3 Replies View Related

Counting Unique Text And Numeric Values With Date Range

Oct 3, 2011

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

View 3 Replies View Related

Macro To Change Date Format To General Format

Oct 17, 2012

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 Related

Some Dates In Format Not Being Converted Into Normal Date Format?

May 23, 2014

I 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

View 9 Replies View Related

Change A Date In Text Format Into Regular Format

Jan 10, 2007

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.

View 9 Replies View Related

Pasted European Date Format Coverting To US Format

Mar 19, 2008

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

View 5 Replies View Related

Extracting A Numeric A Specific Numeric String From A Description Cell

Oct 22, 2009

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

View 9 Replies View Related

Formula For Changing Date Format 3-12-13 To Format D-mm-yy

Apr 8, 2013

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 Related

Convert A Date Format To A Text Format

Oct 6, 2008

Example......

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!

View 3 Replies View Related

Convert General Format Into Date Format?

Mar 23, 2012

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

View 5 Replies View Related







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