# Excel 2003 :: Convert Specific Date Text To Number?

Jun 20, 2014I want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.

View 2 RepliesI want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.

View 2 RepliesADVERTISEMENT

I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.

Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)

Useful information are included !!!

I'll give an example of a record...

Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),

KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,

NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "

Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...

Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:

Function MyMerge(Rng As Range)

For Each Cell In Rng

Temp = Temp & Cell.Value & ", "

Next Cell

Temp = Mid(Temp, 1, Len(Temp) - 2)

MyMerge = Temp

End Function

Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.

Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...

I'm trying to get the number of items against a specific department which are contained in a sheet called "data", the sheet is contained in the same workbook.

There are named ranges for

Department (text)

Date_From & Date_To (in the format dd/mm/yyyy)

Band (Numeric 1 to 3)

Items (Numeric)

Using the following formula I get an #NUM error (using Excel2003)

=SUMPRODUCT(--(Department=A7),--(Date_From>=$B$2),--(Date_To

I have created an online survey, and people choose ten words (skills) out of 24 possible. Please see screenshot. I would like a formula that does this in layman's terms: "If I see the word "Cooperation" in the source cell, then I'll put "03b" into the target cell; but if I see "Managing" in the source cell instead, I'll put "21a" in the target cell, etc."

I've tried a few IF / Then statements, with no success. Screenshot shows the source cell upper right, and the ten target cells below left with two rows of sample results. I'm guessing the same formula can be in each of the 10 target cells.

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.

[URL]

Wondering if there is a simple and concise way to convert a date into text that represents the day number?

So a cell containing 17/09/2006 simply reads as Seventeenth, 18/09/2006 becomes Eighteenth and so on...

I am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)

I am using Excel 2003 and my data looks like this:

Sent Out (A) - Received (B)

01/06/2010 13:00 - 30/06/2010 13:00

02/06/2010 13:00 - 16/06/2010 13:00

09/06/2010 13:00 - 10/06/2010 13:00

21/06/2010 13:00 - 25/06/2010 9:44

23/06/2010 13:00 - 25/06/2010 10:56

23/06/2010 13:00 - 29/06/2010 13:00

I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.

I have a column (A) with numbers like

0

15

30

45

100

115

130

145

200

215

230

245

300

315

330

345

400 and so on up tp 2345

I need to change this into time as;

00:00

00:15

00:30

00:45

01:00

01:15

01:30

01:45

02:00

and so on up to 23:45

Is there any formula which can do this?

I am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items

I am using Excel 2003 and my data looks like this:

Sheet-1 Sheet-2

Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days

1234 01/06/2010 - 30/06/2010 1234 -

4321 02/06/2010 - 16/06/2010 4321 -

1234 09/06/2010 - 10/06/2010

4321 21/06/2010 - 25/06/2010

1234 23/06/2010 - 25/06/2010

4321 23/06/2010 - 29/06/2010

I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.

=SUM(IF(MMULT((NETWORKDAYS(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)

*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))

*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))

I have a very large exported data base to excel. See the Attachment. I have columns on the left with text that needs to stay as text, and rows at the top with dates. These are my filters to sort and view my data. The current problem I am having is some of the quantities in the bulk of my spreadsheet are coming in as text that need to be converted to numbers (that stupid green triangle in the upper left hand corner). I already have developed a macro to format, sort, extract, and place the data so it's more manageable to look through. I need to get rid of this convert to number error before running this macro.

I am looking to develop a macro that will find each of these "convert text to number" errors and convert the text to number. So far I have been unable to find a starting point.

The sample attached is only 1% of the actual size of the spreadsheet, and my exported data base is variable in size.

The only solution I have at the moment is to go to the bottom of my spreadsheet, find the last error, and highlight everything above and done one convert to number.

Sample.xlsx

Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"

View 5 Replies View RelatedI have a date range that looks like this (British date style, not US):

edit: I posted an image but it can't be seen for some reason:

Date Range

2010 11/10 17/10

2010 11/10 17/10

2010 11/10 17/10

2010 11/10 17/10

Thus the first cell show 11 October 2010 to 17 October 2010 I want to return just the first day (the week commencing date):

edit: I posted an image but it can't be seen for some reason:

Week Commencing

11/10/2010

11/10/2010

11/10/2010

11/10/2010

I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.

Code:

Sub ConvertTextToBritStyleDate()

' dd/mm/yyyy (not mm/dd/yyyy)

' Select the cells containing the dates (not the header)

[Code]...

I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.

A B C

7/1/2012 0:00825-CL-A-41091-REG4.00

7/1/2012 0:00825-CL-A-41091-REG6.25

7/1/2013 0:00825-CL-A-41456-REG4.25

7/1/2013 0:00825-CL-A-41456-REG6.50

1/1/2014 0:00825-CL-A-41640-REG4.25

1/1/2014 0:00825-CL-A-41640-REG6.50

3/1/2014 0:00825-CL-E-41699-REG3.00

3/1/2014 0:00825-CL-E-41699-REG4.00

Is it possible to do the following with a formula in Excel...

I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.

The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.

USER FIRST LOGGED IN LAST LOGGED IN

user 1 2 years 20 weeks 54 sec ago

user 2 44 min 7 sec 1 min 37 sec ago

user 3 49 weeks 2 days 17 min 3 sec ago

user 4 5 weeks 2 days 33 min 32 sec ago

user 5 38 min 9 sec 38 min 9 sec ago

user 6 5 weeks 3 days 1 hour 7 min ago

user 7 2 hours 17 min 2 hours 11 min ago

user 8 45 seconds ago 45 seconds ago

Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?

Every day I create many Excel reports that I manually save as PDFs for distribution to my stakeholders. I'd like to automate this process using a macro. I've seen the following code online and have attempted to use it, but receive an error in the Dim MyPDF line of code indicating that the user-defined type is not defined.

I'm using Excel 2003 and Acrobat Distiller 8. I have no problem creating PDFs manually

Code:

Sub Create_PDF()

Dim tempPDFFileName As String

Dim tempPSFileName As String

Dim tempPDFRawFileName As String

[Code]....

Is there a way i can convert the time to a specific number? for Eg. If incell A1 i have the time in following format 01:15:08 (MM:SS:MS)

i want the result in A2 to show 7508

01 minute = 60 seconds

Plus 15sec = 60 + 15 = 75

and 08 miliseconds would give 7508 ??

If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?

View 4 Replies View RelatedI'm trying to convert a date in the following format: 19970121 to 1/21/1997 and am having trouble getting rid of the leading zero in the final version. I also need it to pick up the 2 digits in months that have them from 9 (Sept) through 12 (Dec), though, for use in a VLOOKUP function. Can this be done at all?

Here is the code I've been using so far that doesn't entirely work for what I need it to do:

=CONCATENATE(LEFT(RIGHT(B2,4),2),"/",RIGHT(B2,2),"/",LEFT(B2,4))

where B2 refers to the column the original date form (19970121) is in. I'm still stuck with the leading zero in this code.

I have a requirement where I want my list should auto increment with number only.

For example: I want to fill below text in cell and when I drag the cell( Along the column A) and fill the rows downward it should automaticallly incremented.

Data in one cell say (A1) is like 001_TCO_CM, now I want to drag data present in cell A1 and wants data in cell A2, A3, A4 etc... it should get increment like

A2 = 002_TCO_CM

A3 = 003_TCO_CM

A4 = 004_TCO_CM

and so on...any method or way present in Excel 2003 so that my series should get auto filled.

I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).

I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.

Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii

Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.

Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.

Cell B1 contains a date, then B2 contains a formula that says:

=IF(A1>TODAY(),A1,A2)

A1 contains a green tick and A2 contains a red cross.

What I am trying to add is that if B1 contains no date then B2 needs to be blank.

I tried using =IF((A1="",0),(A1>TODAY(),A1,A2) to get it to show a 0 if there was no data but this doesn't work.

I am using Excel 2003.

I was just wondering what is the formula to convert a date serial number back to an actual date?

View 1 Replies View RelatedI have an 8 character number that I am trying to segment and change into a date.

The current format is: YYYYMMDD, for example 20071003 (today's date)

I am would like to change it to read 10/03/2007 or another easily identifiable date format.

Does clipboard method gettext retreive the text from clipboard only, not number? What if numbers are copied (Ctrl C) to clipboard?

View 9 Replies View RelatedExtract data with differing date formats that I need to convert to something consistent that I can format as a date.

This is an example of data.... all with general format at the moment.

2/28/2013 2:48:53 PM

1/16/2013 10:48:50 AM

12/17/2012 11:59:49 AM

I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?

=LEFT(G9,SEARCH("/",G9)+7)

(The day portion of this date always has a leading zero).

Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003

View 7 Replies View RelatedI have these as input entered as text:

January 01, 2011 January 01, 2011 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010

but I need it in this DATE format:

MM/DD/YYYY

I need it as a formula to be entered into a cell in excel (not VBA)

I have a date in a particular cell, say O5, formatted as mm/dd; e.g. 11/14

I have the following formula in another cell;

=M5 &", Lot# "& N5 &", Expires "& O5, where cell M5 = "CONTROL" and cell N5 = "#####"

Why does the date get converted to a serial number. i.e. CONTROL, Lot# #####, Expires 41592?

My aim is to get the date "11/14" to appear in the cell.

i have number in database 1 and want to convert as date in database 2

04079219920506

04089219920507

04099219920510

18730120010729

18740120010801

18750120010804

18760120010805

result in database 2

407/9206/05/1992

408/9207/05/1992

408/9210/05/1992

1873/0129/07/2001

1874/0101/08/2001

1875/0104/08/2001

1876/0105/08/2001

is it got any formula to convert this number from database 1 to database 2, so far i has attach sample files..

I saw the =WEEKNUM function introduced to Dave196. I tried it but I do not get the desired result. I get "# NAME?" What can I be doing wrong? Also, how can I have Excel take a date and convert it to a number? I want to convert a date into an invoice number (eg. 4/5/07 ===> 20070405)

View 2 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved