Trouble with time formats
Any help appreciated!
I need to work out the average speed it would take to cover a certain
If you covered 10 miles in 28 minutes what would teh average speed be...
Column A: 10 miles
Column B: 28 minutes
Column C: Would return 21.43mph.
I then want to to add time increments of say 15 seconds in rows so that I
can see that if it took 28 minutes, 45 seconds, the average speed column
would return 20.87mph.
I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s),
but I want to format it as time, i.e: 00:28:45 (or 01:10:30)
Is this possible?
Thanks...DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS
fails, pointing to a column with date values. I have looked at all the date
values, and they appear correct. I need to get this excel spreadsheet
loaded to a database table. Is there any way to pinpoint the exact cell
causing the problem? Or, can you think of another way to export the data in
the spreadsheet besides DTS that might not be so sensitive to data content?
...How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion
dates by a certain percentage per year, how do I write the formula. For
example if my project starts May 26, 2010 and lasts until may 26 2014 I want
to add escalation to midpoint at 4% per year.
...Importing Item List for the first time
We are currently change over from Quickbooks Accounting 2002 Pro to MS Retail
Management System and need to know the easist way to import item list into MS
Easiest is to upgrade to QB 2003 or higher. Then follow these instructions;
Otherwise, does 2002 give you the ability to export a report to Excel? If
so, create a report of your inventory items, export to an Excel file and
then use the unsupported RMS Import Utility, again in CustomerSource.
I think you are going to _have_ to upda...Converting Numbers to Time (A second probelm)
Due to several peoples help in this group I learned some very helpful
date/time functions but here is one that has since eluded me...
What I am trying to do it convert...
1/6/1900 22:40:25 to the total number of hours and seconds.
As if the start date was 1/1/1900 00:00:00.
Or in other words I have a
start date of
End date of
Total time of
How do i convert the 1/1/1900 2:12:07 into 26:12:07
Thanks for all your help,
Just format the answer cell as [hh]:mm:ss
"Smonczka" <...disc date for next month
We want to be able to choose a discount date for the next month the discount
is allowed. Our terms are 2% 10th net 15th. We can choose the due date for
the 15th of the next month, but we can't choose the discount date as the 10th
of the next month. All the invoices entered into the system from the 1st thru
the 10th calculate the discount for the current month instead of the next
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the me...combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the
Excel number date? I have text in a couple of cells and a date formula in
another cell(input from a different worksheet). I have the formula =A3 & F3
& A4 in A1 and would like it to read: We went to the station on December 21,
2009 and took the train. However, it is reading We went to the station on
40168 and took the train.
Try something like this...
A1 = Today is
B1 = 12/21/2009
=A1&" "&TEXT(B1,"mmmm dd, yyyy")
Returns: Today is December 21, 2009
...Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou
individually typing up each one
mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450
View this thread: http://www.excelforum.com/showthread.php?threadid=38137
Is there a way to insert it the date into the the worksheet tab (not th
cells in the actual worksheet but the name of the worksheet itself
mark_vi_'s Profile: ht...Time Card setups
We are implementing HR for a client. What is the best practice in importing
Time Code assignments to employees and establishing beginning leave balances?
...Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are
less than 02/05/10 and the name begins with FHLM, heres what i came up
I keep gettin a bunch of ###### or 0, depending on how I move the data
"Senor Martinez" wrote:
> IM trying to create a formula that gives me...Help! Search & Replacing time formats
I have a whole spreadsheet full of fields similiar to the following
10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM
12:20PM 2:25PM 4:30PM 7:15PM
10:00AM 12:30PM 2:45PM 9:15PM
10:40AM 1:10PM 5:00PM 7:20PM 9:35PM
I need to convert all times to 24 hour, and drop the AM & PM.
The later is easy, but how do I do a quick Search & Replace without
incorrectly converting 10:**AM to 22:** etc.
Any tips would be appreciated.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly fro...Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table.
Excel (07) is not recognizing the field as a date. This is a field I
would like to group by in the pivottable. I am aware of all the
techniques to convert this to a date field, but I am searching for an
answer as to why XL pivot tables cannot consume the dates directly
from a sql query.
I have played with bringing the dates back in a number of differenent
formats with no success.
Any insights into this would be appreciated.
Probably coming in as text and yuo may only need to copy an unused cell and
paste sp...Run Time Error 3711
I get this run error occsionally on a machine with Store Operations Manager
running XP Pro. Run Time Error 3711
I would appreciate any help.
...how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually.
Microsoft Publisher MVP
~pay it forward~
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Bob T" <Bob T@discussions.microsoft.com> wrote in message
...Outlook Multiple Profiles at the Same Time
I have two separate icons set up on my desktop to call
Outlook using /profiles switch for profiles A & B. I can
double click one for A, and it opens my POP3 account for
profile A. Then I close it and double click one for B,
then it opens my Exchange Server account for profile B.
The problem is if I don't close the one for profile A and
try to open the one for B, then even if the switch is set
to open profile B, it just opens profile A again. Can
someone tell me how I can have two separate Outlook for
two separate profiles open at the same time?
Adding POP3 to my Exchange S...Suming Up Time over the course of days
I have a sheet the contains event durations on specific days. Some
days have more than one event. I would like to add up all the event
times for each day on a second sheet and (line) chart the results
The list get longer with each event.
Tuesday Nov.2 2010 10:16 am 00:37:22
Tuesday Nov.2 2010 08:28 am 00:15:48
Monday Nov.1 2010 08:30 am 01:03:07
Sunday Oct.31 2010 06:36 pm 00:09:20
Sunday Oct.31 2010 06:17 pm 00:03:26
Sunday Oct.31 2010 05:27 pm 00:07:42
Sunday Oct.31 2010 11:15 am 00:27:19
Sunday Oct.31 2010 08:56 am 00:45:26
Saturday Oct.30 2010 12:44 pm ...use a time value in a bar graph
i have values in a series
I would like to use e5 in a bar graph it gives values which would make sense
only to excel
If the axis is formatted as time, it shouldn't be a problem. If it doesn't
choose the scales to give clean divisions, you can choose an appropriate
unit on the axis, such as 03:00.
"pdfrone" <email@example.com> wrote in message
>i have values in a series
> c5 9:01
> d5 21:01
> e5 =(d5-c5...Solved! (This time for sure!
Wait... Bullwinkle? Boris? Hmmm....
Boris, I owe you. I never in a million years would have guessed it was the
icon, but that's exactly what it was. I just got an e-mail from the client.
The system now works 100%.
If by chance you are on facebook, please add me as a friend, or just send me
an e-mail sometime - firstname.lastname@example.org
And say "Hi!" to Natasha for me!
I don't know Bullwinkle, and Boris is not my real name, but I'm glad it
On Sat, 17 Apr 2010 14:06:40 +0200, Boris Pauljev
<email@example.com> wrote...One link breaks every time I open a workbook...
I have 3 workbooks tied together with links. One is a source only, the other
two push and pull data.
One of the sources, Payroll Master, on opening updates all links fine to the
source only workbook but not
to the third workbook (which is in the parent directory). Every time I open
it it prompts me to show it where the Payroll Entry is. Doesn't matter if I
hit cancel and
do Edit Links, I can change it there, but will still lose it upon closing
(the link appears in the box and looks ok, just won't update...)
Try going Edit->Links. Break the link and re-establish it? That m...Looking up a date
Trying to get this to work, but no success:
The value in A6 is a function; not hard-coded.
It seems to work with one date and one result vector, but not two, and
ultimately I will need several, maybe 7 or 8.
You can't use functions in array constants.
>ultimately I will need several, maybe 7 or 8.
Why don't you just create a table in ascending order by date:
.............A.......Daylight saving time Strange behaviour
I have created an application where I am using
CTime( int nYear,int nMonth,int nDay,int nHour,int nMin,int nSec,int
nDST = -1);
Constructor to change the dates to UTC before saving them to
I have set my timezone to Easterntime(US&Canada) which is 5 hrs behind
I entered the following date, 01\11\2006 12:00:00PM.When its converted
to UTC,the date its showing is 01\11\2006 4:00:00PM.
In 2006,DST ended on 29\10\2006,thus ideally it should show
This behaviour is shown for dates ranging from 30\10\2006 to
Analysing the issue,I checked my...Count the number of times a character appears in a field
This is a multi-part message in MIME format.
How can I count the number of times the @ symbols appears in a text
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<META content=3D"text/html; charset=3Dus-ascii" =
http-equiv=3DCon...Format Date 05-28-10
How do I format the date to show May, 2010? The closest I can get is May-10
from the drop down list.
> Hi, all,
> How do I format the date to show May, 2010? The closest I can get is May-10
> from the drop down list.
Custom date format:
Custom Format mmmm, yyyy
Gord Dibben MS Excel MVP
On Fri, 28 May 2010 09:05:01 -0700, Malcolm
>How do I format the date to show May, 2010? The closest I can get is M...Charting an intermittent time series #2
I am trying to chart a data set. The x axis is an intermittent time
series (e.g. 1pm, 1:15pm, 4pm, 11pm, 11:05pm, etc)
I have one data item which is scaled from 50 to 300, another from 1 to
20, and a third which is 15 to 100.
Is there a way to make a time scaled chart with the data on multiple y
...Excel Date Formatting
In Excel the regional settings are set to short date
format. However, if you try and format cells individually
with a date, the date changes, but when the spreadsheet is
closed and the reopened the formatting reverts to the
short date format! We are using Excel 2000 with sp3 on XP
(If thats any help!) If anyone has any ideas why this is
occuring i'd love to know!
Are you saying that the same person on the same pc opens the file and the date
format has changed?
If it's another user (or another pc), then it could be the way excel picks up
the date format if ...