Tracker
HOME    TRACKER    Excel

# Date Conversion To Number

## I'm trying to convert a number from word to a number in excel. Example: 10-12 input in word to a cell in excel. Unfortunately it treats it as a date an gives me 39002. I want to be able to use both numbers and add them together to get -2 not 39002. How to format the excel spreadsheets in order to complete this calculation.

Related Forum Messages:
Conversion Of Gregorian Date Into Islamic Date
if it is possible to convert Gregorian date into Islamic date by using a formula or VB code in excel? If yes, what is it?

Number Conversion
I have two sets of data that I need which is obtained from one source.

Basically, the stats I am working with in excel are Processing Time (minutes/seconds) and Volume Per Hour. I desperately need a formula that will allow me to convert the processing time into Volume per hour.

Example: Average Handle Time, 7min 7 seconds = x per hour. If it was just 7 minutes flat I could simply divide that into 60. I also can't make it 7.7 and divide that because 7.7 doesn't equal 7m7s.

Does anyone have any thoughts on a way to do this? I found a conversion chart online that allowed me to do this work manually 'Conversion Chart: Fraction of an Hour to Minutes', but I haven't determined a way to make a formula out of this.

Character To Number Conversion
I have a spreadsheet which I automatically generate using VBA. One of the columns lists account numbers. After the macro is complete, the account numbers are non-numeric and left justified. However if I select one of the account numbers by clicking on its cell, and then edit the account number in the formula window, it changes the cells property to numeric and right-justifies the cell.

Date Conversion
I am trying to convert numbers from three cells into a date in one cell. I tried using the =DATE function, but it did not work. In A1, B1, C1, I have 1, 15, 2002. I would like cell D1 to take the info from the preceeding three cells and display "January 15, 2002"

Presently, I have A1...C1 formatted for "General." D1 is formatted for "Date." Yet I get the answer "August 23, 1907" (or 2792 as 'General').

Date Conversion Macro
I use the following codes to convert YYYYMMDD into DD/MM/YYY

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

I use this on quite a regular basis, I would like to convert this to a macro so I can simply select the cell/cells and press the button.

Firstly is this possible and can anyone provide a script?

5 Digit Date Conversion
IS THERE A FORMULA TO CONVERT THE 5 DIGIT # INTO ITS CORRESPONDING DATE?

Date Time Conversion
Basically my requirement is to find out the time difference in terms of minutes between two days of time.

For example. the difference between(in minutes) 27/04/2006 10.01 and 29/04/2006 11.02. The reasults i want is in terms of minutes only.

Text To Number Conversion In Validation List!
I have 2 sheets;

Sheet1 (Also named "TicketNames") and Sheet 2 ("Day1").
On TicketNames I have 4 Columns the first 3 columns with about 5 names per columns.
Each list of five are given a specific Name ("One","Two","Three"). The last column is just
a list for Day1 dropdown menu. (3 names, "One", "Two", "Three").

On Day1, I have a validation list on A1 which will allow the user to select either ("One, Two or Three").

On B1, another dropdown menu, but it will only show the items related to the selection they made on A1.

My problem is that on A1, instead of having "One" show up, I want it to say "\$1" for One dollar. But because it is a number not a text it won't allow me to use a validation list using this.

I'm using Excel 2007

Recognize Conversion (word To Number) And Multiplication Factors?
I'm trying to make a table of the total amount of a liquid used throughout the day. Here is what I am trying to do: In cell D4, I want to be able to enter something similar to the following: 3cup+2bottle+1liter

and by doing so, Excel can automatically recognize that 1cup is 8oz, 1bottle is 17oz, and 1liter is 34 oz because of the reference chart provided on the side. Also, it would be able to notice the 3, 2, and 1 amounts so it would multiply accordingly so it would know to do this: (3*8)+(2*17)+(1*34)

and then put the calculated amount in the cell. The correct answer should be 92oz. Is there a way for Excel to recognize the conversions (i.e. whenever it sees 'cup' it will multiply by 8) and multiplication factors (i.e. 3, 2, 1)?Is there a formula I can enter that I can just "drag" down to the upcoming days in column D?

I know I can just do something like this: (3*G4)+(2*G5)+(1*G6).

IBM DB2 Date Format Conversion
I have come across an issue and I am not sure what the best way is to achieve my goal or if it's even possible. We are importing dates that are in IBM DB2 Date format. Example: the date is imported as 1060318(C/YY/MM/DD), which is is actually 2006 March 18th. I would like to convert 1060318 to something that Excel can format into March 18th 2006.

Serial Date Conversion To Semi String Data
I need this: 38842.8287152778 to be converted into 05052006_075321PM. This is the date for May 5th 2006 @ 7:53:21 pm in VBA. How do i get there from 38842.8287152778?

Min & Max (30+ Cells Selection) + Date Conversion
Excel won't take more than 30 cells when using Min or Max function. I am trying to get min & Max of the following:

=min(M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU 3,BW3,BY3,CA3,CC3,CE3,CG3,CI3)

Is there an alternate to this? The selection is every alternate cell starting M3, O3 & so on.

Also, All of the above reference columns have date stored in "2002 Aug 02" format. Is there a quick way of selecting the columns automatically & converting it to DD-MMM-YY?

I tried various methods but nothing works unless I manually enter the date. I am looking for an easy way because I have 100's of sheets to fix with 30+ columns. It will be a big and tedious job if I try to create a separate column beside each and then convert using a formula.

Date Conversion And Use A Variable Of Type Long As A Search Criteria
How to assign a variable of type Long to the below code.

1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF(\$A\$2:\$A\$200=""" & searchField & """,\$C\$2:C\$200))"

2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF(\$A\$2:\$A\$200= searchField,\$C\$2:C\$200))"
1) code failed because I want the variable searchField to have a data type of LONG.

2) code failed because it seems like excel thought the search criteria is of same searchField.

How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?

Calculate Number Of Days Between Two Date Within A Current Month Including End Date
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.

Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:

5/10/2009 to today is -9

5/22/2009 to today is 3

Extract Character From Date Format & Number Of Days Between Date
I have two questions regarding date format and hope you can provide input.

1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?

2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?

Date Countdown: Results Of The Number Of Days Between Today And A Future Date
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?

Calculate End Date From Start Date & Number Of Weeks
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?

Number Of Days To Add To Date Based On Weekday Of Date
I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008

Formula For Date Minus Date = Number Of Days
I was wondering if anyone knows how I could enter a date in one cell, then another date in a second cell and in a third cell have it have it so it minuses the first date from the second and calculates the difference outputted in number of days.

Example: 04/31/08 - 04/01/08 = 30 (days)

Formula Date Result Showing Number Not Date
I’m trying to resolve this issue and would appreciate some help.

Scenario
I have a tracking sheet that tracks development of work to be completed by individuals.
I have a due date column that shows when the work should be completed.
Once the work has been completed, the user enters his completion date.

I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.

Problem
When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.

How can I eliminate this being shown as it throws out my average development day calculation?

Convert Number Representing Date To Real Date
I 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.

Week Number For Date Relative To Date Range
Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.

Date Serial Number To Actual Date
I have a macro which copies the values from one cell to another. But, while copying the macro changes the date format to a serial number and this doesnot happen all the times. Below is the code I have. I want the macro to automatically convert the date from from serial number to dd/mm/yyyy. I tried quite a different ways, but none of them seems to work. Below is the macro I run to copy the contents from one cell to another.

Sub History()
'
' Macro: Backup results from prior rounds. Please run this macro after selecting the cell where the backup is to be done.
'
cur_row = ActiveCell.Row
If ActiveCell.Offset(-(cur_row - 10), 0).Value <> "History" Then
MsgBox ("Check whether the active cell is the designated history cell. History operation failed")
Exit Sub
Else
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & ActiveCell.Offset(0, -5).Value & " "..........................

I have just been told that one of our servers is being replaced this weekend. I have thousands of hyperlinks in excel docs that reference this server. Other than manually reestablishing the links one by one to the new server, is there a way to do some kind of global change to replace the old server name with the new server name?

Or, is there a way to convert these hyperlinks to hyperlink formulas that would omit reference to the server and thus not be broken during the conversion?

Grasping at straws because I cannot envision having to relink these one by one.

Scoring Conversion
If I have a scoring grade for different categories (8 in total) & the grades looks something like that: 1a=500, 1b=400, 1c=300, 2a= 250, 2b=210...& so on till 4c, but with different numbers for each category.

I need to come up with a total score of points if a person is given a certain grade from the above. ex. if they scored 2a on a certain category, 1c on
another & with all the rest of 8 categories scored. The number i need to come up with is the total points.

Time Conversion
I have an spreadsheet that is monitoring the productivity of agents, as well as the time that it takes to complete each task. My workforce keeps time as .50 = 30 minutes. So 7 and half hours is actually 7.5. Agents are putting the actual time of it took such as 15 minutes however I would like to insert a formula that will convert this for me manually. I know I could give them a conversion sheet, but it took quite a while to get them to actually be able to work in excel. So I would like to add this formula.

Currency Conversion
I am working on a sheet that logs sales enquiries and quotations. The quotations may be made in either Dollars, Euros or Pounds. However, I want the 'reporting' column to be Pounds. I would appreciate any advice on the simplest way of achieving this.

Imagine column A is dollars, column B is Euros and column C is pounds. Most of the time the quotations are done in pounds and the other two columns will be blank. Also, there will only ever be one figure quoted per row. Would it be easier to create a fourth column (D) to consolidate the three separate figures together? We are using fixed exchange rates for the year, so to convert to pounds we will multiply any dollar quotations by 0.5 and any euro quotations will be multiplied by 0.69.

Fractions To Decimal Conversion
I have received help on this topic in the past and I though I had solved the issue, however I realized recently that my formula will not work on any fractions larger than 1 inch. I am converting machine threads in fraction form to a decimal equivalent. here is an example of the what the entry looks like before it is converted.

ex,
1/2-20 3A (becomes .5000)or
3/4-13 2A (becomes .7500)or
1-14 3A this one will not work with my current formula (should be 1.0000);

Conversion From 2003 To 2007
My company used to have Excel 2003. I had a PivotTable in a worksheet that accessed data in another, completely different worksheet. When we converted to Excel 2007, I saved many of my Excel files from .xls to .xlsm. My PivotTable that once accessed data from another .xls file is still trying to access that same .xls file. What I want, though, is for it to access the new .xlsm file.

PDF Conversion Code
The following codes publish the worksheets "Estimate" & "Photos" to a PDF file, it all works great accept that the sheets remain selected in the WB after the sheets have been published. How do I unselect the estimate and photos sheets?

Yen Conversion Macro
I have a worksheet with values in yen in the C column. My boss (who will not get it through her head that I am not a programmer) wants a macro that will take a yen/\$ conversion rate (e.g., 88 yen/\$) in cell J1 and convert all the amounts in column C into dollars with a 5% markup and rounding up. If I was doing this as a worksheet calculation rather than a macro, the calculation would look like this:

2000 To 97 Conversion Issue
I have built a nice employee time tracking device in Excel 2000. It is pretty self explanitory ("IN" time, "OUT" time, total time, etc). What I am having problems with is the VB code that is behind the "OUT" time. In essence, once an "OUT" time is entered, the ms error message box is overwritten to ask what type of break the person took (No Lunch, 1/2 hour, 1 hour) and takes the response and implements it into the Total hours calculation. Works great in 2000, but the users are getting problems in 97. i don't have 97 and can't seem to trouble shoot it. Could some guru with 97 have a look at this and see what exactly I am missing in 97.

The code is a manipulation of Ken Getz's brilliance by Stratos. Which can be viewed at the following: http://groups.google.com/groups?hl=e....warwick.ac.uk

Amount To Words Conversion
I am using excel for invoice making...In the end i have a total amount. I need to type it in words for every invoice at present.
How can i convert it to words automatically? Is there any formula to it?
Or is there anything else? Please suggest me friends.

Time Conversion Formula
I have 4 columns:

A B C D
0:00 1:17 56.15 0.06

Column A is in hours:minutes:seconds.milliseconds
Column B is in hours:minutes:seconds.milliseconds
Column C is in seconds.milliseconds
Column D is in hours:minutes:seconds.milliseconds

I would like to represent all these columsn in this format:
hours:minutes:seconds.milliseconds

Is there a simple formula I can use? I was looking at the CONVERT function, but it doesn't appear to work for milliseconds.

Time Conversion/calculation
I need a formula that will calc the following: using Excel XP

#MIN.
INLUNCHOUTHRS
8:00 AM04:00 PM 8.00
8:00 AM304:30 PM 8.00
8:00 AM304:30 PM 8.00
8:00 AM304:00 PM 7.50
8:00 AM605:00 PM 8.00

Automatic Currency Conversion
I'm using excel 2000

Is there anyway to format cells such that I can enter values as Euros and they automatically convert to £

eg If I enter 14.60 (Euros) it automatically displays as £10.00, my conversion rate of 1.46 is constant throughout

I know I can do the copy paste special divide thing but the above would be a much nicer solution

Numeric To Text Conversion
I often use vlookups that fail because of numeric/text discrepancies. I was always taught that if you're not going to do math on a field, it should be text. Such as zip codes or social security number.

Anyway, I know the easy way to convert text to numbers is paste special > multiply by 1. But what's the easiest way to convert numeric to text?

Currency Conversion By Dropdown
I have a sheet of prices in USD, and want to be able to convert those numbers to Euro. I realize there is no formula to do this...

I was going to list USD prices in cell A (which I was going to hide), and then in B have a formula to multiple the contents of cell A with the exchange rate.

Is there a way I can create a dropdown cell and based on the contents of the dropdown change the currency items in cell B?

Formula For Months To Years Conversion
Here is my formula:

=E3*(1+E9/365)^(365*E5)

Cell E5 contains a place for you to put in the number of years you want

I want to modify this formula so that it calculates months instead of years, but still be based of a 365 day calendar year.

Data Conversion From Month To Weeks
I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on.

I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones.

So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)

Measurement Conversion Format Cell
What is the custom format cell type I need to create to make cells in inches (such as 0.125, .25, .375, 3.125) into the format of 00-00-0/0 where the 0/0 is 1/8 of an inch? So, 4.25 would become 00-04-2/8. 62.25 inches would become 05-02-2/8.

Data Conversion/Cell Formatting
I'm having a little trouble figuring out how to do some conversions. For instance, I would like to know how to convert a decimal number like this:
1.800

which is a ratio of 1.800:1 to display like this:
9:5

So, when the 1st value changes to something like this:
1.778

It would be displayed like this:
16:9

Likewise, a ratio of 1.333:1 would be displayed as 4:3.

I also don't want it display in Time format as Excel has a habit of converting values with colons to time format.

I've been looking on the internet for a spreadsheet that lists the various units of measure with conversion factors to other units. I'm not looking for the formulas, just a chart.

Currency Conversion: Convert In GBP
i have an order book which lists the value of each order against the currency it was ordered in. So in column A i have a list of currencies (THB, GBP, AUD, USD, the user clicks the appropriate one) and in column B i have the figure (23.50). I want a formula that can convert all these in GBP so that i can report the order totals in one currency in column C. =(IF A1="THB", B1/50, "") can i adapt this to include all variables? I will add a table with the currency conversion rates.

CInt() Conversion & Inputbox
as i understand CInt() converts STRING variables to INTEGER variables. I am trying to create an inputbox, with input validation. So, if the user types in an integer, it will continue to a msgbox; if they type a number in string format, as in 'two', it will convert this into an integer and again display it in a msgbox; if they press nothing a msgbox will appear telling them there is no input; and if they press cancel the programme closes.

Numerous If Conditions? GPA Conversion
I am trying to create a spreadsheet that will take all my grades from the semester (I'm a college student) and compute my GPA. I managed to get a weighted average for each class, but the grade is on a scale of 1-100. I need to convert that to a letter grade, and then a 4.0 scale. How can I set it up so that it will return "A" if 90<Grade<100, "B" if 80<Grade<90, etc.? I'm apologize if this is something simple, but I've been trying to find the right function for a long time.

1. How do I make it a global defualt on all my excel spreadsheet to ALWAYS have the text on the top of the cell and never at the bottom

2, How do I make it imppossible for hyperlinks to appear in my document. I print up lists with emaill addresses, and the hyperlinks drive me crazy since they launch an email. When I remove the hyperlink, the text appears on the bottom of the cell (see above note). I want to be able to type in email address and never deal with hyperlinks every

Excel Compatibilty Issue With File Conversion
I have developed a Macro which on the event of a button press which will generate an email and convert the Spreadsheet which was being worked on into a csv file.

Now, this works great in Excel 2007, I press the button and I receive the email and in the attachment is a csv file which I open and everything works great. Now when I have tested this on an Excel 2003 machine. The email comes through fine and the attachment is a csv one, but when I come to open it, I get the message, 'The file you are trying to open, '12345654321.csv', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening file. Do you want to open the file now?'

On clicking yes, it opens the spreadsheet in an excel format?

Type Conversion Errors In VBA Code
I have an existing code that runs almost flawlessly but I am running in to formatting cells error (or at least I think that is the problem).

For example, here is a line of code where my error lies:

If Worksheets(FirstSheet).Cells(Counter, ColtoMove) = MoveIf Then
'DO SOME ACTION

If I put my cursor on MoveIf it shows the expect number of 4500016239…….BUT it errors!

When I step through the code and go to the worksheet (or go to the left side of the = in the above code the data in the cell is “4.5E+09“.

I’ve attempted to change the formatting of this cell range to “Text” by the following With statement:

With Worksheets("CreateOrderFormsData")
.Range("AC:AC").NumberFormat = "Text"
End With

Note: if I verify the cells formatting after the above code is ran by rt click on the cell, format cells the Number field is set to “CUSTOM” and the Type: field is set to “TEXT”. Is this the problem? Please read on.

But that did not seem to do the trick…..actually if I now go to the left of the = again (which is now set to text) the data is “###########” in the cell, in the formula bar the number is the expected 4500016239 though but the CODE STILL FAILS!

Am I on the right track…..can anyone assist with this error please?