I trying to create a schedule on my 2002 excel. I placed all the times in HH:MM AM/PM format. But somehow...I don't know what I was doing...all the times instantly appear as decimals on the cells. The times I inputted still appears in the cell's formula bar. I tried changing the cell's format to my prefferred time format, but the cells wouldn't budge.
View 4 RepliesHow to be able to convert decimal hours into time format.
Here are the examples:
12.2 = 12:02:00
98.41 = 98:41:00
300.42.1 = 300:42:10
and so on and so forth...
How to convert decimal numbers which represent minutes into time format? Like this
Sheet3 GH1MinutesTime212.9729729700:13:00325.9459459500:26:004116.756756801:57:00
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:
=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))
However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedI'm putting together some data for a pivot table.
In Column Q I have a list of products. In column E these products are listed a number of time (depends on the number of regions which may change). What I am looking to do is have column A show "1" the first time the item group is in the list, "2" the second time...up to 12. Basically I'm looking to have a month (#1-12) appear in column A next to the item. If there are 3 regions then then each item will appear in the column E list 3 times. I'd like each group of 3 items to have a "1" the first time they appear, "2" the second...etc.
Here's an example....
1 - Item#1
1 - Item#1
1 - Item#1
1 - Item#2
1 - Item#2
1 - Item#2
2 - Item#1
2 - Item#1
2 - Item#1
2 - Item#2
2 - Item#2
2 - Item#2
3 - Item#1
3 - Item#1
3 - Item#1
3 - Item#2
3 - Item#2
3 - Item#2
I have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.
I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.
I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!
I need to convert regular time to decimal time. example regular time in A1
is 1:38 I need that number to be converted to decimal time = to 13:63. add
12 to the hour and divide 38 into 60 to get the minutes. Our time clock
prints on a 12 hour basis and I enter to excel in decimal time.
I've got a time difference from 8:00AM - 12:30PM as 4.30 I'm trying to get the minutes, .30, converted into a 6 minute increment, .5. Is it possible to do this and if so how would it be done? Below is a chart of how the time is converted from 6 minutes increments into decimal form.
6 = 0.1 36 = 0.6
12 = 0.2 42 = 0.7
18 = 0.3 48 = 0.8
24 = 0.4 54 = 0.9
30 = 0.5 60 = 1.0
I need to separate a list of times from a telecommunications bill. I have 170,000 line of data.
The Times are represented as below.
eg
46 46 Sec
59 59 Sec
100 1 Min
159 1 min 59 sec
200 2 mins
502 5 mins 2 sec
1256 12 mins 56 sec
3456 34 min 56 sec.
How do I separate them to a decimalized per min value.
I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit.
Here's an example, Column C contains:
Dan Parker
John Doe
Dan Smith
Jill Smith
So if I search on *Dan*, the function should return a count of 2.
I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.
How do you convert decimal time to standard time?
....Hrs
A1 21.70
A2 7.20
A3 37.38
I know 1.5 is 1:30 but I don't know the formula.
Cell C9: 1.25773
Cell C10: 20.0
Cell C11: 2.25%
Cell C15: =C9+((C10*C11)/10000)
C9 is a user entered value, currently formated general
C10 is a user entered value, currently formated decimal with one decimal
C11 is a user entered percentage, currently formated percent with two decimals
C15 is a formula calculation, currently formated general
I've tried formating cells to general (and/or) text and the values appear to be correct but still don't show what I want.
The most popular entries for C9 will be:
123.12
123.123
1.1234
1.12345
Any of the above could have one or more trailing zeros.
I would like C15 to show the same amount of decimal places that the user enters in C9. If user enters one, two, three, four, five, etc...decimal places in C9 then show the same amount of decimal places in C15 after the calculation is done and include any trailing 0's that are needed to match the number of decimals in C9.
I've tried different If statements with custom format to try to get the format of C9 transfered to C15 but haven't come across the right way to do this.
Is there a way to format a number into 2 decimal places and when you select the cell you wont see the true value?
For example:
316.2696
I still see the 316.2696 when I click the cell. Instead of 316.27 only.
I need to control the amount of decimals allowed by the entry of another column same row so I do not have to format the cells everytime to get the correct decimal places. I need all my data in one table so I can do pivot tables.
View 14 Replies View RelatedIf I enter 2.5 into a cell, and then change the format so that there are no digits after the decimal, the number changes to a 3.
2.4 correctly rounds to 2, but shouldn't 2.5 round to 2???
I thought that even numbers were supposed to round down, and odd numbers round up when there is a 5 right after the decimal.
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
View 4 Replies View RelatedI have a problem with the currency format with 4 decimal places.
Sub go()
Range("C5") = Range("C5") + Range("C4")
End Sub
as you can see it adds the conecnt of "R4" to what is in "R5"
If works fine when the calls are formatted as number with 4 decimal places.
example if R4 is 0.1111 and R5 is 0.2222 after running the macro R5 becomes 0.3333
But if the cells are formatted as currency and 4 decimal places the then excel returns $0.3300
I need to never have a trailing zero be displayed but still have decimals displayed (we can use up to 3 digits for the example) Here's a list of test cases
Value______Display
1.234567___1.235
1.234______1.234
1.23_______1.23
1.230______1.23
1.2________1.2
1.200______1.2
1.0________1
1.000______1
1.020______1.02
1.0002_____1
0__________0
0.001______0.001
0.0001_____0
The only time I want to see a 0 is when the answer is 0 (or rounded to 0) or when 0 is required in the middle. This is for patient safety if you are curious. 2.0 when printed out or written by hand can be mistakenly read as "20" in the heat of things, thus 2.0 should be written/printed as "2" (no decimal on the end) not as "2." even, just "2"
I'm trying to make a shift schedule template, and it's all done except that i cant sum the hours the way I think i should be able to.
I've attached the file for reference.
I've added in some hours for myself as an example. I have 47.5 hours in the week. I've got 3 Rows, In Out and Hours. I've formatted the In and Out the way i want them, but I would like the Hours row formatted as a decimal, so that 7 hours 30 minutes would show up as 7.5.
I'm also having trouble with the totals column at the end. Although D8:J8 total 47.5 hours, it's adding them as 23:30.
Data from a SQL database represents time of day as a string of x's when imported into Excel. With CLEAN, I found that noon is -0.5.
This means that the calculation being used for noon is (24 * -0.5), or -12, meaning "go to the end of the 24 hour day, then go back half a day to noon." Similar calculations work other times of day.
Time entries that start in one day and end in the next are not allowed, so there is no problem with a change in day,
How can I convert this to the AM/PM time that my users need?
I am trying to convert time (formated as hh:mm) to a number with 2 decimal places.
Example
A1 7:51
B1 - want the number to show 7.85
Will then multiply this by an hourly wage to get total $ paid.
Is there a way to format a cell based upon a condition? If the cell value is <1, I want to show two decimal places. If the cell is >1, I want to show zero decimal places. I tried to use the conditional formatting, but there is no option for this.
View 3 Replies View RelatedI'm writing a macro that will automatically change the display in a column of cells.
The input in the cell would be a decimal value, (e.g. 1, 1.25, 1.5, 1.75, 2). As of now, I think the only input options are whole numbers and 1/4, 1/2, 3/4 fractions.
After inputting the decimal value, the cell should update to display the value as the following string format :
Input: 1.25
Displays: 1-1/4"
The purpose being to enable fast data entry while displaying in the desired format.
Here is where I know to begin for the automatic update:
Code:
Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'
'
'Code
'
'
Application.EnableEvents = True
End Sub
I have the following code
Code:
Private Sub ComboBox1_Change()
TextBox1.Text = Range("P" & UserForm1.ComboBox1.ListIndex + 3).Value
TextBox2.Text = Range("N" & UserForm1.ComboBox1.ListIndex + 3).Value[code].....
In TextBox3, the user will input a percentage as a whole number, IE: 5 for 5%, but when the update is made, it is writing the "5" as 500% and is writing 50 as 5000%.
If there is already a percent in the cell it calls up IE: 50%, it shows up in TextBox3 as .5%
I need to be able to convert big hex numbers into decimal numbers. The problem that I have with this is that when you put a hex number that includes letters excel considers it text. And if the number is too big, you can't convert it to decimal format, and when you try to, it comes up with the #NUM! error.
Number: "78652385098375A879FF747C9847A00" without quotes
I have employee experience data in decimal format in different columns like 4 years 6 months = 4.6
Is there a way to Sum the Values of cells so that it gives total experience in decimal format as same??
A1
B1
TOTAL EXPERIENCE (EXPECTED TOTAL SUM)
4.6
4.6
9.0
5.5
5.5
10.10
2.11
2.11
5.10
Is there anyway to be able to bring decimal point forward without affecting the numbers using any formatting, which will still be there even after refreshing the query.
For example, 0.0531 to 53.1
Is there any way to change the decimal point from the usual period symbol (.) to a comma (,). The reason being, i have a structural design spreadsheet and the new terminology is just so, changed from 00.00 to 00,00 I have tried cell formatting but it just uses a comma (,) for numbers in their thousand and it wont do what i need.
View 3 Replies View RelatedI am having problems applying custom format to numbers that need to be formatted.
custom format use is 0",00". When I apply the custom format above to whole numbers, I achieve the results I want:
0.00
20.00
40.00
becomes
0,00
20,00
40,00
However, when I apply to numbers with decimal places
0.20.......
how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?