Converting General String Month To Date Format?

Jan 18, 2013

I have a cell which says November , 2012 in General format

In the next cell i want a date of the following month . Eg : 1-Dec-2012.

I tried =DATE(RIGHT(G4,4),LEFT(FIND(",",SUBSTITUTE(G4,",",",",1))),1)

View 1 Replies


ADVERTISEMENT

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

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

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

Automatically Format Date Cell As General

Jan 8, 2007

I have a cell that is constantly being changed from a date to a number. Excel automatically formats the cell as a date when a date is entered but if I re-enter a number it leaves the format as a date.

Is there anyway to automatically change the format back to general if a date is not entered.

I had this bit of code obviously it dosent work.

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E46")) Is Nothing Then
If IsDate(Target.Value) Then
Target.NumberFormat = "d/mm/yy;@"
Else
Target.NumberFormat = "General"
End If
End If
Cancel = True

View 4 Replies View Related

Date Format Error - General Number Field

Feb 18, 2013

I have a spreadsheet with a column full of dates

I used the formatting wizard to make all dates the format of dd/mm/yyyy but, now, when I put a date in the column it throws up a number '41313' instead of a date, even if I manually input the date in the same format as above.

Also, I have tried to turn cells back into general number fields, but this doesn't appear to be changing it back to a date format..

There are HUNDREDS of dates I might have lost if I can't get them to start showing as dates again!

View 2 Replies View Related

Convert Serial Date Time To General Format

Jul 28, 2007

I use software that exports date/time stamps in the format of: MM/DD/YYYY hh:mm:ss.s
I like to keep the data in this general format so that we can keep it consistent and it can be useful to others using other software.
I recently had to do some date/time stamp manipulations where I added 61 days to a dataset because the data was erroneously set to a diferent calendar. (Still don't know how that happened but it isn't relevent to this issue) When I set up a formula to add 61 days, it appears that it automatically converted it to the excel serial date/time format before adding the 61 days. Now I have a column of data that is mostly general format but has this portion in serial format. How can I get the serial format changed back to general so that the entire column is in the same format?

View 9 Replies View Related

Converting String Dates From UK To US Format

Oct 4, 2007

I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:

Simple example of the problem:

Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub

Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.

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

Converting A Day Date To A Month And Year

Oct 14, 2008

I am trying to use a pivot table to analyse some data. The base contains a "day" date..ie 01/05/08, however I want the pivot table to summarise by the month..ie May 08. I have used the "=month(cellref) command, and it returns the correct month number. However when I try and format this to "mmm" or "mmm yr" to get a month I can then cut and paste, it always retunrs the month of JAN.

View 3 Replies View Related

Converting Day Of Month To Full Date

Mar 18, 2014

I'm looking for away to convert the value of cells with a numbers that represents the days of the month as 1 - 31 to the full date.

As if the cell value is 3 convert to 3/3/2014 and so on. I tried changing the cell format which does covert it to 1/3/1900, then I experimented with DATEDIF but didn't get anywhere.

View 9 Replies View Related

Formula Converting Time To General Number?

Mar 13, 2013

Looking for a formula to convert time (00:00:00 AM) to a general 4 digit number (0000). Example 08:30:00 AM ---> 0830. Tried using format cells but no luck.

I was able to convert a standard number to time using the following formula: =TIME(INT(H2/100),MOD(H2,100),0)

View 2 Replies View Related

Dates - Show Month Only, And Actually Be The Month Only (not Just Format The Date)

Jul 28, 2008

I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.

How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?

View 9 Replies View Related

Converting String To Date With DateValue?

Mar 9, 2014

I am trying to check my sheet names to see if they convert to a valid date format. If it doesn't, then I want it to check for the next criteria (ex - if it equals "Page2"). With the code below I keep getting an error of type mismatch. Code is below.

[Code] ......

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

Converting Binary Date To Date / Time Format

Jun 9, 2014

I have a list of dates stored via the following :

20100101 how can I convert this to date/time format to show 01/01/2010?

View 4 Replies View Related

Military Date Format Needs Converting To Civilian Date Value

Jun 21, 2013

I have a program that exports the date as 20120621 (4 digit year, 2 digit month, 2 digit day). I have tried =datevalue(cell) but it returns #value!. I have tried =today()-(cell) and it also returns #value!. Is there a formula or something I can do to covert this field to a date?

View 3 Replies View Related

Converting To Date Format

Aug 22, 2008

I collect data from a data historian and I'm having difficulty with a date format. The collected data is saved in a txt file and imported to Excel 2007.

The date returned by the historian is the following format

9/30/2007 8:45:18.000 PM

This format is not recongnized by Excel as a date format but as a text box.

How can I change this to a date format without having to change all the dates and times manually (over 10 000 dates)?

View 9 Replies View Related

Converting Numbers Into Date Format...

Jan 15, 2007

I have a spreadsheet which has the date set out like this: 20070101

If there any way i can change this to either 2007/01/01 or 01/01/2007?
I have tried using the date format (in the cells) but it just comes up with ######## i tried expanding the cell but no luck, this is very importand that i do this as its for a customer.

View 9 Replies View Related

Auto Converting Numbers To Date Format?

Dec 20, 2012

I've been having a problem lately with Excel automatically converting all of the cells on my spreadsheet from standard number format to dates. I've created several spreadsheets for calculations and all the cells in the number formats that I wanted. It's been saved and actually reopened and used several times. I opened it this morning and now half of numbers are now converted to dates. I've had this happen several times on several different spreadsheets. Why is this happening? Is there a setting somewhere that I need to change?

View 1 Replies View Related

Converting Date Format In Formatted Column

Oct 9, 2013

I have a column that contains dates from our system that displays in "mmddyyyy" format (i.e., 10121999 or 8121998). How do I convert this to a 10/12/1999, 8/12/1998 formatted column?

View 1 Replies View Related

Loop Converting Date To TEXT Format

Jul 21, 2009

i'm trying to convert a column (P) from date dd-mmm-yy, subtract 5 days, and paste value as YYMM using a macro. I've borrowed bits from other macros and producted this but it's not working (and i've played around with it to the point it doesn't make sense anymore!)

Option Explicit

Sub ADD_REPORT_DATE()
Dim LR As Long, i As Long
Dim tempValue1
Dim tempValue2 As Date

Worksheets("Report").Select
LR = Range("P" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1

Set ActiveCell.Value = Text(ActiveCell.Value - 5, "YYMM")

Next i
End Sub

View 9 Replies View Related

Converting Imported Data To A Date Format

May 1, 2002

I am importing data from my AS400 in Excel, the dates are coming into the spreadsheet as numerics. How do I convert these numbers into a date format? i.e. 3202002 should be 3/20/2002, how do I get the number to that format?

View 9 Replies View Related

Converting Date And Time From Odd Text To Standard Format?

May 10, 2014

I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.

View 4 Replies View Related

Excel 2010 :: Converting Persistent Date Format?

May 27, 2014

I have a Column of dates in this format: 5/19/2014 8:08:44 AM I am trying to get this to read 20140519 or yyyymmdd It doesn't matter what format I change to, it will not update unless I activate the cell and then tab or enter out of it. I have 3000 rows to convert. with this either by setting or VB Sub?

Excel 2010

View 3 Replies View Related

Converting Crazy Date Format To An Excel-useable One

Nov 27, 2009

I have a long series of dates (month and year) formatted like this:

1969.051969.061969.071969.08

In other words, these are May '69, June '69, July '60 and Aug '69.
I cannot get excel to recognize them properly. How can I conver these into an excel-recognizeable date format?

View 9 Replies View Related

Converting Text To Military Date Time Group Format

Apr 12, 2014

I am trying to find a way to have excel recognize text data input as date/time.

[Code] .....

Where 02 Is the Date, 2020 is the time (military) Z is Zulu/GMT, MAR is Month and 14 is Year. I believe excel recognizes

[Code] .....

But I have a spreadsheet (on a confidential system) with thousands of entries that I need to convert. Also, the people I have working for me are not remotely. I reject the idea of entering data that way.

I am also trying to keep the display the same format: ddhhmm"Z" MMM yy

View 9 Replies View Related

Turning General Numbers Into Time And Minutes And Multiplying For Month

Mar 3, 2014

I run a report from a scheduling software that gives me the total hours and minutes for a weekday for my bus routes. For instance, 253 hours and 23 minutes for one day. Now though, I need to calculate how many hours and minutes for the entire month. The report will only come out with 253h23. I need to somehow take that, turn it into time and then multiply it by 20 (January weekdays). But I need real hours and minutes and I'm having trouble getting them.

View 7 Replies View Related

Excel 2010 :: Converting Text Into Date Format - Error Occurred

Mar 20, 2012

Formating Date Issue

--

Excel or Access version:Excel 2010

Computer operating system:Windows XP

Sample data:No sample data

Formula(s) right now:Mar-5-2012

Current result(s):Mar-5-2012

My goal:To convert the above text into a date format.

Error message:Can't format the way I want it.

How error occurred:No error message

Generated in:Excel

View 3 Replies View Related

Sum Cells With General Format

Mar 12, 2009

I have a group of cells in E2:E4 that are 144.00 in, 240.00 in, and 72.00 in and are formatted as general.

I get these values when I export data from an AutoCAD program to Excel and I want to sum these values to get the total length.

I want to sum the three values and have the result be the same (i.e., ###.## in). The number of values can go to 30, so I need a simple line of code, =SUM(???)

View 9 Replies View Related







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