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

thanks

- Change to show time
- Express time as a decimal and add
- How to round down to nearest 5 minutes, time calc?
- Date shows up as 5-digit number
- Show fractions as fraction in combo box
- Number Format:Is there a way to have 25,000.00 show in the cell as 25
- Userform with Time Field displays Time as Number
- Enter a Percentage and have it show up as a dollar amount in the same cell
- Setting MP3 track times as 1:23 etc
- 'If' Formula in a time window to account for buffer time of a 5 min window
- Format Cells To Show Time As "PM" By Default
- Need a Time As Length and not as an AM/PM
- Check If Time Greater Than 24 Hours & Convert To Decimal Time
- Formatting a time problem
- How to display 5.5 hours instead of 5.30 when adding subtracting
- HOW TO HIDE/SHOW PICTURE AS PER LOGIC IS NOT DESCRIBED PROPERLY.
- Need formula that will read time as min:sec in excel
- Getting symbols to show up as numbers in linked document.
- How to show (time) of cells to the listbox of a form
- Help on function VLOOKUP and TIME
- Multiple price for items 1 = $25, 5-15=$22, 16 or more=$20????
- Negative numbers shows up as )8.20( instead of (8.20)
- Problems changing mm:ss to minutes decimal
- Show time in 12hr mode but no AM or PM

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?

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?

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

Arrggghh..

Thanks in advance...

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!

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

calculate as 25,000.00?

calculate as 25,000.00?

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 SubI 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.

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

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

I need to indicate that even if it 5 mins early its considered "on Time" and 5mins late etc..

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:Thanks in advance!'Add Lunch/Shift Related Times 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 PMIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

Thanks

CB

formatted to show answer as 4.5 instead of 04:30

WILL SHOW AND 0 FOR HIDING. THE PICTURE.SHOW/HIDE OF PICTURE WILL DEPEND ON

LOGIC OF A FORMULA OR CELL CONTENT ETC.

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

Any suggestions?

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

Thanks,

J

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.

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?

chart plots the info it shows and prints as )8.20(. Already tried the

regional settings, loaded the latest Office 2003 updates, etc. Please help.

Thanks.

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!

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

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

Thanks in advance