Free Microsoft Excel 2013 Quick Reference

# Show time as .25, .5 etc

I have a pay sheet that cells A1-A8 add up the hours worked and gives me say 7:30 (7 and a half hours). Is there a way to display this as 7.5 as this is how the pay office like it formatted?

thanks

## Related Results

### Change to show time

I have a LARGE (22,000+ rows) excel worksheet with results from a query I ran
to show admit and discharge times. However the times are not in the right
format to perform calculations so I can measure the length of stay. For
example the time 2:15 a.m. is 215....because my query is pulling data from
our mainframe there is little I can do about how the data comes in...I have
already looked to see if I can convert the data or compute the data, but have
been unsuccessful. I have also tried to format the cells so it will show
time as 02:15, but this is not working either. What is working for me is
this....the function TIME. I can put in the function as =TIME(2,15,0) and it
will display the time 2:15 thus enabling me to perform the correct
calculations. But what is the best way, if any, for me to have the entire
spreadsheet done without having to go into each individual cell (which is not
feasible) ? Any suggestions?

### Express time as a decimal and add

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.

Any suggestions?

### How to round down to nearest 5 minutes, time calc?

Just when I think this spreadsheet is working, I find something slightly
wrong <g>.

There are 2 "totals" cells. The first totals cell shows total exact time,
the other shows it rounded down. The complex rounding down formula I got
several weeks ago does the rounding bit but doesn't show total time
correctly. I didn't have enough hours until now for this to show up. After
24 hours, it subtracts 24 and just shows time as if it were a clock time
(i.e., rather than 25 hours, it shows 01h00, as if it were 1 o'clock a.m.
instead of a sum of 25 hours). Anywho, the initial rounding formula doesn't
let [h]:mm work, but the regular rounding one I recvd yesterday in the group
allows [h]:mm to work perfectly but isn't rounding down, just rounding in
general. In the example below, it is rounding _UP_ 2 minutes.

i.e., the real total in the test hours I plugged in shows these results:
72h58m
while the cell that rounds shows:
73h00m

As this is talking about overtime, this is not good. I need it to round
down but hopefully just to closest five minutes. I don't want to get paid
for 2 extra minutes because that would freak out the overtime people
eventually <g>, but rounding down to 72h55m, for example, would be perfectly
fine. I'd lose out on 3 minutes of overtime but this way, no problems with
my employer.

Is there a way to get the formula, which is in this particular case is:
=ROUND(SUM(J2:J31)*96,0)/96
to round DOWN to nearest 5 minutes?
Hopefully the cell format of [h]:mm will still work, too.

Thanks so much! Eventually this darn sheet will work and I will be able to
share it with the rest of the floor here! :oD

### Date shows up as 5-digit number

Hi. How come my Excel shows my date as a 5 digit number? I already double checked the date format. It still doesn't want to cooperate.

Arrggghh..

### Show fractions as fraction in combo box

Greetings
I have a combo box which pulls its list from a list on one of many sheets. The list is formatted as fraction, & the fractions show carrectlyof course 1/8, 1/2, etc.
When I go to use the combo box they show correctly, but as soon as I make a selection, the fraction is converted to a number .33333, .5, etc.
How can I get the fraction to stay a fraction?

Nevermind!
As I typed this...
Format the fractions as Text in the sheet & they stay fractions!
Hopefully this will help someone someday...
Thanks anyway!

### Number Format:Is there a way to have 25,000.00 show in the cell as 25

Is there a way to have 25,000.00 show in the cell as 25 and still have it
calculate as 25,000.00?

### Userform with Time Field displays Time as Number

I have a userform that has a time field on it and then the userform displays the time as a number.

Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.

Below is the code that I currently have.

```Private Sub FirstRecord_Click()
lCurrentRow = 2
RowNumber.Value = lCurrentRow
' Show contents of new row in the form:
PSA.Text = Cells(lCurrentRow, 2).Value
cboTypeofContact.Text = Cells(lCurrentRow, 3).Value
DateofContact.Text = Cells(lCurrentRow, 4).Value
TimeofContact.Text = Cells(lCurrentRow, 5).Value
End Sub```
I am not sure how to change the code to reflect the time instead of a number. I am thinking that it needs to be formated somehow.

### Enter a Percentage and have it show up as a dollar amount in the same cell

Hi

I want to be able to enter a percentage discount in cell F17 of my quote template and have it show in F17 as the equivalent dollar amount, ie; the discount of 2.5% of \$1000 is entered into F17 as 2.5% but shows in F17 as \$25.00. Is there a function or formula I can use for this?

Thanks

### Setting MP3 track times as 1:23 etc

Hi...I'd really appreciate help with the following.
I'm no XL expert and to be honest, I tend to use Excel (I'm on Office 2007) most of the time for generating lists of MP3's or music tracks containing among other things the time-length of each track.
I have one very long XL list of these!
Here's the problem:
I simply want my track times to show in the number format: 'X:YX' eg 4:50 - four minutes, fifty
However, my lists show ZERO + X:YZ
Taking the above example, that would show as: 04:50

For my own reasons, I'd prefer 4:50 but going into 'Custom' I just can't find this format.
Nearest is mm:ss which seems to be default for the above.

Trying to change each one by 'clearing contents' then typing in the desired format doesn't 'take'. Just reverts to the original in a second!

Since I've lot of MP3's/music tracks in the list with the 'wrong format', changing them one by one is just a pain and well..see above for result!

Surely there's some way I can 'Find/Replace' all the track times to the format I want?
Any help or suggestions would be welcome.

Many thanks

### 'If' Formula in a time window to account for buffer time of a 5 min window

I have been getting an error with this formula in which I am trying to account a buffer time of 5 mins( + or -) here and there. Right now I am doing this manually by conditional formatting But is there a way to include this into the formula in column J.
I need to indicate that even if it 5 mins early its considered "on Time" and 5mins late etc..

### Format Cells To Show Time As "PM" By Default

When i enter time into a cell it makes it AM by default. By default i mean if i dont put AM or PM next to the time. Is there a way to make it show PM by default? Thanks in advance.

### Need a Time As Length and not as an AM/PM

Hey all,

I have code that determines the amount of time between two date/time entries. For example:

Shift: 0530-1730
Shift Start = 0530
Shift End = 1730
Shift Length = 12 hours

The return on the 12 hours, however, shows in Excel as 12:00:00 PM. I, however, need this time as simply: 12:00 hours. I was under the impression TimeValue did this, but I'm still getting the AM/PM result. Any ideas?

```
VB:

ShiftBegin = Date + TimeSerial(Left(Range("G" & Target.Row), 2), Mid(Range("G" & Target.Row), 3, 2), 0)
'Determine Shift Length
ShiftLength = Left(Range("G" & Target.Row), 4) - Right(Range("G" & Target.Row), 4) 'Finds the difference between the 2 times
ShiftLength = Sqr(ShiftLength * ShiftLength) 'Squares then square root removes any negative values
If ShiftLength > 1200 Then 'deals with any short shifts that return very large shift lengths
ShiftLength = (2400 - (Left(Range("G" & Target.Row), 4)) + Right((Range("G" & Target.Row)), 4))
End If
If ShiftLength < 100 Then ShiftLength = "01:00" 'deals with shifts less than 1 hour
If ShiftLength > 999 Then 'Deals with any shifts less with less than 4 digits i.e. 8 hours)
ShiftLength = Left(ShiftLength, 2) & ":" & Right(ShiftLength, 2)
Else: ShiftLength = Left(ShiftLength, 1) & ":" & Right(ShiftLength, 2)
End If
ShiftLength = TimeValue(ShiftLength) 'Changes string to a time value

Range("AB" & Target.Row) = ShiftLength 'Here I want it to return the time as "duration" i.e. 12:00 hours, and not a time of
day 12:00 PM

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Check If Time Greater Than 24 Hours & Convert To Decimal Time

Hi,

I have multiple excel worksheets with times greater than or less than 24.

I need to convert the datetime to hours if greater than 24.

For example, I have time 779:55:00. when I click on the cell it shows 2/1/1900 11:55:00 AM.
All I need is time as 779.5.

Can you help me with this.

Thx

### Formatting a time problem

I posted this issue last night regarding a problem I am having with recording times for junior athletics. I received some replies from several members however I may not have properly detailed my problem. I have now worked out how to post a sample table so I will try to better explain. Column C shows times as they are presently recorded (formatting (custom) mm:ss.00). The PB columns contain formula's that recognise when a time is better and attributes at PB (Personal Best). This is proving an issue with the data entry people who are used to the format I have shown in Column E. The problem with the format in E is that is will not recognise the better times and therefore does not recognise a PB. Any ideas?

ABCDEF1DATE400MPB800MPB2320/10/20081:22.451.22.45427/10/20081:21.44PB1.21.44530/10/20081:22.561.22.56

Thanks

CB

### How to display 5.5 hours instead of 5.30 when adding subtracting

when adding or subtracting times (13:00-08:30) how do i get the answer

### HOW TO HIDE/SHOW PICTURE AS PER LOGIC IS NOT DESCRIBED PROPERLY.

HOW TO HIDE/SHOW PICTURE AS PER LOGIC ? SAY IF A CELL VALUE IS 1, THE PICTURE
WILL SHOW AND 0 FOR HIDING. THE PICTURE.SHOW/HIDE OF PICTURE WILL DEPEND ON
LOGIC OF A FORMULA OR CELL CONTENT ETC.

### Need formula that will read time as min:sec in excel

Im trying to create a format within excel that will let excel read the time
as min:sec instead of hours:sec or hours:min. Then I want to create a
formula to automatically convert that cell into a separte cell showing all
min:secs as seconds only.
Any ideas? I dont think it is possible due to the default within Excel in
regards to the ":" sign.
Need any and all help.
Thanks

### Getting symbols to show up as numbers in linked document.

I have a linked document, only 2 sheets. On the first sheet there is a column for + or - signs, I need a formula for the second sheet to show the + as a 6 and the - as a 5 instead of just the symbols carrying over.

Any suggestions?

1st post, please let me know if any clarity is needed.

Thanks,
J

### How to show (time) of cells to the listbox of a form

first of all i want to say sorry to my last thread.

i have a cell "A1" that i inputed a time value, in excel it shows as time value, but when i used it to a listbox "listbox1.additem (A1)", the listbox show a numerical value "0.3424234"
how can i show time value in the listbox.

the time value is not changing.

thanks.

### Help on function VLOOKUP and TIME

Working on time and motion study in excel 2003. Need a function to recognise data as a letter e.g. A = Admin, E = Email etc then multiply by time (as in 5 min to each task). Unsure if VLOOKUP and TIME correct functions to use and how to set up.

### Multiple price for items 1 = \$25, 5-15=\$22, 16 or more=\$20????

Hi all,
Just found this forum. Thanks in advance for any advise you can provide now or in the future.

The problem:
I'm working on a supply database. I have seperate cells titles "What I need to Order", "On-hand Qty", "Low Limit", and "High Limit".
The on-hand qty will be entered, and If it below the low limit it will inform you with "Order now".If the On-hand is higher than the High Limit you will be informed "Over Stocked".If the On-hand is between the Low and High Limit it will show "Good Sparing".I can get it to do everything to this point. I can subtact the On-hand from the High Limit to tell me "What I need" cell.
Now the problem, I want it to calcualate the cost. Suppose my item is 1=\$25, 5-15=\$22, 16 or more=\$20.
I want my "What I need" to compare prices for the price breaks. i.e. If I need 10 items then it would be (10 * \$22=\$220), or (20 * \$20=\$400).

Last hitch... My items are not a set percentage. They are just different prices for all the items. I want the formula to be universal with all the cells. i.e. have seperate cells showing the diffent price breaks so a non-excel expert (my kids) can just fool around with the data in the cells and not with the formula. P.S. By no mean, do I call myself an expert. I'm just a novice too.
Make sense?

### Negative numbers shows up as )8.20( instead of (8.20)

I've checked all the formatting, the data shows up as (8.20) but when the
chart plots the info it shows and prints as )8.20(. Already tried the
Thanks.

### Problems changing mm:ss to minutes decimal

OK, so I've been over this and I just can't get it to work like I want. I have a column with the amount of time it took a student to take a test (formatted mm:ss). In order for this column to show up correctly, even though it is formatted as mm:ss i have to enter, say 6 min and 25 sec, as 0:06:25. The column beside the time column has the formula =L1*24 and is formatted as a number, so I can get the decimal version of the time (in this case 6.42).

Here is my problem. In order for the decimal formula to work, I need to enter in only 06:25, which displays the time as 25:00. Is there a way I can keep the formatting to mm:ss and input 0:06:25 (so it shows up correctly) and still get the formula to work? If I enter in 0:06:25, the formula gives me an answer of 0.11

Thanks for any help!

### Show time in 12hr mode but no AM or PM

I read in another post how to show time in 12hr mode without showing AM or PM, but in that method it would show a small p for PM. Im looking for a way to show 12hr mode but NOT show the AM PM without having the time show in 24hr mode?

So if it is 1:00AM I need to see 1:00
if it is 1:00PM I need to see 1:00