Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Format Second(Now) In Vba 2 Return Seconds In Tenths *

How do you format


to display tenths, hundredths, or thousands of a second? Must be in code since it will never be applied to a cell.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Converting Minutes, Seconds And Tenths Of Seconds Into Seconds
I want to change 1 minute 24 seconds and 5 tenths into a seconds number. So basically it would come out as 84.50 seconds.

View Replies!   View Related
Time Format: Format Cells To Contain Minutes, Seconds And Hundredths
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09

View Replies!   View Related
Format For Time In Seconds
I have a report that tells me how many items are completed every "x" seconds for a person. What I would like to do is when I enter that information into my spreadsheet is to have it calculate those seconds into minutes and seconds. (i.e. I will enter 105 (seconds) into a cell and I want it to display 1:45. Is there a formula or setting in the cell format that can do this? The problem I am going to have is that I will have to enter in each cell as seconds every time and then need it to convert to the minute and second format.

View Replies!   View Related
Format A Cell To Do Minutes And Seconds
is there away to format a cell to do minutes and seconds? then get an overall sum?

View Replies!   View Related
Format For Degrees, Minutes & Seconds
I have a column in excel which includes numbers like (12 13 14.5), and I want to format like this (12 Degree Sign, 13 Minute Sign, 14.5 Second Sign).

View Replies!   View Related
Format Cells As Fractions Of Seconds
I am trying to set up a workbook to record times for our swimming club. This will involve recording the swimmer's time as minutes, seconds, tenths and hundredths. I would then want to be able to compare the new time to the swimmer's previous personal best and calculate the difference.

View Replies!   View Related
Format Time To Show Hours, Minutes & Seconds
is there a way in VB to make it fast, and tidy, clear the contents of all unlocked cells on one sheet ?

View Replies!   View Related
Convert Decimal Minutes & Seconds To Total Seconds
How can I Convert Decimal Minutes & Seconds To Total Seconds?

Here is the sample

View Replies!   View Related
Convert Hours, Minutes And Seconds Into Total Seconds
I trying to figure a formula to convert time on a phone call eg. 01:01:21 into total seconds (3661). Phonecalls will never be more than an hour long but the spreadsheet I will be supplied with (havn't got it yet!) will display them in the 00:00:00 format.

View Replies!   View Related
Convert H:mm To H:tenths
How do I get a straight conversion from h:mm to h:tenths? As soon as it hits 23:59, the clock start over in the tenths column. ie...39:27 should read 39.4. I'm currently using this formula: =(T10-INT(T10))*24


View Replies!   View Related
Converting Seconds Or Minutes:Seconds To A Number
How would I go about converting - for example:

0:22 (formated general) to a number = 22


1:30 (formated general) to a number = 90

View Replies!   View Related
Hours:minutes:seconds Convert To Seconds
I am trying to convert a number of "hours" "minutes" and "seconds", to give me a result in seconds only, in order that I may then financially cost the amount of time spent on a task. (A time and motion costing exercise)


Represents 1119hours:48minutes:36seconds spent on a task.

I can manually convert this to 4031286 seconds, but it just takes too much time !!!(sorry!)

The data is extracted from a database which is unable to split the hours,minutes,seconds into seperate fields, which prevents me from using three cells in excel.

View Replies!   View Related
Convert Seconds Into Hours:Minutes:Seconds
Is there an easy way of converting seconds from using a time into hours, minutes, seconds

View Replies!   View Related
Convert Seconds Into Hours, Minutes & Seconds
I have a spreadsheet that imports data relating to phone call durations. The information imports as a total number of seconds taken.

What I need to do is to change that number of seconds into hours, minutes and seconds.

Changing the format of the cells doesn't work.

I've tried everything I can think of, and run out of ideas.


23 needs changing to 00:00:23
96 needs changing to 00:01:36
268 to 00:04:28
9374 to 02:35:14

View Replies!   View Related
Lookup To Return Value And Format
My workbook contains two worksheets a scorecard template and a data sheet. I use the lookup function to retrieve statistics from the data to populate various fields in the scorecard template. The value can be numeric, currency or percentages depending on the lookup criteria. Although the data shows the correct formats in the data sheet, when the lookup retrieves the value it only shows the value as number format. Is there a way the cell can retrieve the cell format information as well as the value and apply it to the cell

View Replies!   View Related
Return A Value Based On Cell Format
I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:

Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".

View Replies!   View Related
Return Numbers With Specific Format
I am trying to return a number with a specific format. I need Excel to look in a particular column and when it finds the entry in the correct format to return it in a new column. I want it to repeatedly return the number to the new column until it finds another number of the same format in the original column.

View Replies!   View Related
Time Conversion: Convert A Time From Hours/Minutes To Hours/Tenths
When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?

View Replies!   View Related
"If" Function -need Return Data In % Format
I had more of an excel questions.

Let's say

A1 "=D1"
A2 "=D4"
A3 "=D7"

so baiscally the row reference increases by 3. How could I write a cell formula for A1 and so that I can just drag and drop it down for the rest of the cells?

View Replies!   View Related
Add Seconds To Timer
Below is my code to display a seconds count down timer in a textbox.

What I want to be able to do is have another textbox where I can enter the number of seconds that the timer will count down.

I don't know how to take a value from the textbox and make it like
#12:00:15 AM#

How would I do this?

Sub warmTimer()
Dim InitialTime As Single
Dim FinalTime As Single
InitialTime = Time
FinalTime = InitialTime + #12:00:15 AM#
txtTimer.Text = Format((FinalTime - Time), "s")
Loop Until (Time >= FinalTime) Or (Skip = 1)
txtTimer.Text = "Time complete"
End Sub

View Replies!   View Related
Macro To Run After 30 Seconds
Is there a way to make a macro delay for like 30 seconds before it does its thing? What I'm trying to do is have something turned off before the spreadsheet is saved, and after the spreadsheet is saved have a macro that runs 30 seconds afterwards to turn said feature back on. On error this macro will simply terminate.

View Replies!   View Related
Converting Time To Seconds.
I have the below macro. It is used to translate time to seconds (i.e. 0:20:04 minutes to 1204 seconds)
This works well, but i just want to know 3 things.
First, can the new data (seconds value) be added to new worksheet (worksheet 4) of the workbook the original data is taken from, instead of a whole new workbook?
Second, can this be done without having to save the original workbook first?
Third, (Im sorry i know this is VERY simple) but this macro is part of a larger macro, and i just want to know the code to hide the screen updating as the macro is running.

Sub Refresh()
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End Sub.........

View Replies!   View Related
Subtract Milliseconds Or Seconds
I have a cell that has data formatted in this style:



I was wondering how I might go about writing a formula that would subtract say 2 seconds, or 400ms from that value.

View Replies!   View Related
Converting Minutes To Seconds
I have a spreadsheet that I need convert times from minutes to seconds. Such as 03:44 minutes to 224 seconds.

View Replies!   View Related
Fx To Calculate Seconds From Minutes

I have to manually enter X based on the number of times the time stays the same before going on to the next minute.



I would like to automate the above so that X is caculated, however ; X can be any number from 1 - 25 and is completely random.

View Replies!   View Related
Random # Genration -every 3 Seconds
I want to generate Random # which should keep generating after every 3 seconds.

View Replies!   View Related
Countdown For 10 Seconds In Form
I have been searching for quite a while now and cannot figure this out. Basically I have a label named lblCountdown, and that is where I am stuck. Every Do loop, example code, whatever I try won't work. I want a countdown for 10 seconds in my form. The form should show the countdown.

View Replies!   View Related
Delay A Change For 20 Seconds
Im using live data and was wondering if its it possible to trigger B1 to change its value from no to yes 20 seconds after cell A1 changes its value from 0 to 1?

View Replies!   View Related
Trigger Macro After 15 Seconds
I want to trigger a macro that refreshes a pivot table but I only want to trigger the macro after 15 seconds. The reason is that I am pulling the source data from access mdb so I want only to refresh the data once the data is pulled.

View Replies!   View Related
How To Have A Message Pop Up And Blink For A Few Seconds Then Go Away
With all the formulas working (or at least through testing so far they are), I am now in the make the worksheets fancy mode.

I want to have a message pop up when the leader of the meet changes. I would love it if it would blink for a few seconds to alert the individuals looking at the worksheet but then it would disapear so as not to be annoying.

For example:
In event 1, team a is first, and team b is second.
Event 2 finishes, and I post the results in the spreadsheet.

In event 2, team b takes over the lead and team a is now second Place.

I wold like a message to appear (for a few seconds) saying (and blinking something like "Team A, Just Took Over 1st Place!" and then goes away.

View Replies!   View Related
Changing Minutes To Seconds
i need a hand to change the following. how do I get 02:23.5 ( Mins, seconds, 100th of a second) into just seconds.

View Replies!   View Related
Time Difference In Seconds
Given any two timestamps with the format

dd-mmm-yyyy hh:mm:ss AM/PM

How to calculate the total time difference in seconds.

For e.g. calculate the time difference in seconds for following

07-Nov-2009 00:00:01 AM
06-Nov-2009 11:59:59 PM

Answer should be 2.

View Replies!   View Related
Pause For 0.5 Seconds (half A Second)
I need to pause for 0.5 seconds (half a second). How can I do that. applicatiom.Wait can only be used for whole seconds and a FOR ... NEXT loop is CPU dependant.

View Replies!   View Related
Recording A Cell Value Every X Seconds
how can i record a cell's value on a different tab every x seconds?

the value is updated every y seconds and i would like a list in column A of the cell's changing values.

every refresh may produce the same value.

View Replies!   View Related
Average Minutes And Seconds
I have some time data that is dumped into an excel file from a phone system. I'm trying to average the data, but I get division by zero errors. The data is originally formatted as "general" and when I convert it to a time format I still get the error. I've attached a small version that has just the time column.

View Replies!   View Related
Message Box Appear On The Screen Only For 5 Seconds.
Is there a way to have a message box appear on the screen, but for only 5 seconds? and then disappear?

View Replies!   View Related
Jump To Next Cell After X Seconds
1. Create a worksheet for each set of tables (Sheet1: 2x table, Sheet2: 3x table etc)
2. In field A3 (e.g.), I'd type (Formatted as text): 2x1=
3. In field B3, he'd have to type the answer. To prevent him from using the cell as a calculating tool, it will be formatted as text (courtesy of Prevent Someone From Entering A Formula In A Cell)
4. He'll start the test by clicking a button that says "START THE TEST" (or something more exciting, like "Earn some money!"). I'd like a macro to select the first cell (This I can do, believe it or not! :-)) and then, after, say 5 seconds, jump to the second cell, then the third etc up to the last cell (2x13=)
5. Despite the timer, the active cell must be allowed to be changed to the following one (one down) by pressing "Enter" (In other words, if the timer is set at 5 seconds and Jonathan enters the answer in 1, he may then press enter and move to the following cell immediately)
5. He can only enter the answer in the cell while highlighted and I'll have the whole sheet protected once the macro has done its run to the end or once he's answered the final question and pressed "Enter".

View Replies!   View Related
Show Form For X Seconds
Is there a way for a form (I am using it as a warning dialog) to show for X seconds (10 or 15) and then hide or hide upon user selecting button to hide it?

I have noticed that if the user form is active then an error comes up when a script tries to run.

View Replies!   View Related
Play Sound Every X Seconds
I would like to create a macro that plays a sound every 45 seconds or so. In other words, I would like to have a countdown timer that counts from 45 seconds down to 0 seconds and at the end it plays a sound file(could be mp3, wav, etc) to signal the end. After that it should automatically reset itself back to the start of 45s and count down again. This would be repeated about ten to eleven times. What is the easiest way of doing this? It's too confusing. Lets say the audio file was located in: C:my soundseep.wav

View Replies!   View Related
Days, Hours, Minutes And Seconds
Does any know how I can convert 29.18:20:18 to total minutes?

View Replies!   View Related
Time As A String And Converting To Seconds
I have an ACD program that gives me a time in "mm:ss" format, or in ":ss" fomat. I have 2-3 problems with charting this data.

1 I am inheriting the production of charts from someone else, with old data being displayed by seconds only I am supposed to keep this display.

2 the data that is in mm:ss format is recognized as being in full date format, for example 2:14 is recognized as 2:14:00 AM instead of 2 minutes, 14 seconds.

3 the data that is in :ss, ie :44, is recognized as a string.

Any thoughts on how I can convert both cases to a integer seconds? I know for example that I could mid() the second condition, but I am stuck thinking about the first condition.

I can't adjust the way the time comes in, unfortunately. It is an auto-generated export of the data from a switch.

View Replies!   View Related
Imported Document Time To Seconds
I have an imported docment that stores the data as excel time. I need the data in seconds. I changed the format to [s]. This was great, exactly what I needed until I imported the document in to Access. Access changed the data back to time format. So here is my question: Is there any way to paste [s] data and save it as an text? or Is there a formula that I can change the data into seconds? I have searched the forum for a couple of days and went to the formula help page, but there was nothing that addressing my issue.

See Example below

Original imported data:
0:17:57; 1:53:00
10:04:11; 34:54:36
127:19:04; 804:26:33
297:58:37; 127:10:07
72:59:56; 273:31:29

Changed to [s] (desired values in Text or number format):
1077; 6780
36251; 125676
458344; 2895993
1072717; 457807

View Replies!   View Related
Convert Hours To Minutes And Seconds
I am trying to use the Mod function to convert the following:

I have time in hours in A1 as 12.66 and I need it to convert to 12 hours in A2, 39 minutes in A3, and 36 seconds in A4. What formula can I use to achieve this?

View Replies!   View Related
MsgBox To Show One Time For Five Seconds
I need a MsgBox to show one time for five seconds, and not repeat.

Here is a sample of where I'm at. But this code repeats.

Public Sub timebox()
MsgBox "Let Me See You Work"
Application.OnTime When:=Now + TimeValue("00:00:05"), Name:="timebox"
End Sub

View Replies!   View Related
Degrees Minutes Seconds Formatting
How do I custom format a cell to display degrees minutes seconds. I now there is a way. I found in on the web once but can not find it again. The formating from cpearson does not work the way i want.

For example if in cell a1 I input 50.2536, I want to to format the cell as 50 25' 36". opefully this can be applished without any formulas. Either soem code or formating would be great. The last time I found it I thought it was something like ### ??'/??" but that did not work. Thanks in advance. The reason I need it, is to use the convert_decimal function I found on the microsoft website.

View Replies!   View Related
Macro To Remove Seconds From Timestamp
I have a column of cells in my worksheet that look like this:

1/1/2007 10:53:31 AM
1/1/2008 8:35:46 AM
1/1/2008 8:35:54 AM
1/10/2008 1:39:26 PM

As you can see this is a timestamp in the format: m/d/yyy hh:mm:ss a/pm

The problem is that Im trying to import this data into another program that accepts the same format without the SECONDS field: m/d/yyy hh:mm a/pm

Since I have thousands of rows of data it just isnt feasible to go through and manually remove the seconds data from each cell.

Is there a macro or some other shortcut that can do this?

The ultimate goal would be to have the data above end up looking like this:
1/1/2007 10:53 AM
1/1/2008 8:35 AM
1/1/2008 8:35 AM
1/10/2008 1:39 PM

View Replies!   View Related
Convert Decimal Value Into Total Seconds
I have a formula that reads like this


The formula is used to calculate a comparative run time at an elevation of 5000 feet. The time the run was in is 11 minutes and 40 seconds. What I need to do is convert the 11.40 into total seconds which would be 700.

Now the actual 11.4 in the forumala is from a cell reference (say D14). It's whatever time the member plugs in (and so is the 5000, this represents the members altitude).

I need the time to stay in a decimal format because I have a huge database that I perform a lookup on to find the point value for it for another calculation seperate from this one. If I were to plug 700 in place of 11.4 my calculated result would be 683.43 which is exactly what I need.

View Replies!   View Related
Calc Minutes And Seconds Difference
Spreadsheet tracks race times for cross country races and needs to be able to
calculate improvement time in mm:ss (or zero if less than previous race).

Also,it would be nice to be able to enter times without using colons or have
to reference the field as h:mm:ss. The race result will always be mm:ss and
the last two digits will always be seconds, the result will never be more
than 60 minutes so hours are a real bother.

A1 (8:52, m:ss) - A2 (8:40, mm:ss) should = 0.12 (12 seconds)

Presently I am using the following: =TEXT(A2-A1,"h:mm:ss"), I only want to
display m:ss, hours are meanlingless

View Replies!   View Related
How Many Seconds Has My Little Girl Been Alive Task
I'm trying to construct a gimmick which will represent how many days my little girl has been alive and am having some trouble trying to figure out which route to take in trying of constructing my timers

I'm trying to represent how many seconds my daughter has been alive in real time,

She was born on 01/06/2005 and I want to be able to see the ticker ticking away on my screen rather than it being static

In fact I would like to illustrate how many years, months, weeks, days, and seconds Loren has been on this earth,

If successful I would like to take it one step further and do this with words as well as figures

View Replies!   View Related
Extract Minutes And Seconds From A Time Value
I have a formula that gives me a time value (ie 12:53). I need to perform another formula that will use this data but I need the numbers seperated (12 in one cell and 53 in another)

In essence, what would be the formula to pull off the minutes number and the seconds number into individual cells. I know anything is possible in Excel, I just can't figure this one out.

View Replies!   View Related
Copyright 2005-08, All rights reserved