Times out of sequence

In Access I can use the DateDiff function to calculate the difference of 
time between two times and it will also show negative time! in excel I have 
only been able use if(time1<time2,1+time1-time2,time1-time2). Could I 
somehow use automation to access that function... ? or is there an easieer 
way in excel to show negative time!

Thanks 


0
jayes33 (7)
1/14/2008 7:41:52 AM
excel 39879 articles. 2 followers. Follow

4 Replies
691 Views

Similar Articles

[PageSpeed] 49

Hi

If you use the 1904 date system, Tools>Options>Calculation>1904 system, then 
you can display negative times.

Be aware that there is a difference of 1432 days between the 2 system's base 
dates, so do not mix dates in the same workbook.

-- 

Regards
Roger Govier

"Jays" <jayes33@hotmail.com> wrote in message 
news:OqbjDDoVIHA.484@TK2MSFTNGP06.phx.gbl...
> In Access I can use the DateDiff function to calculate the difference of 
> time between two times and it will also show negative time! in excel I 
> have only been able use if(time1<time2,1+time1-time2,time1-time2). Could I 
> somehow use automation to access that function... ? or is there an easieer 
> way in excel to show negative time!
>
> Thanks
> 
0
Roger
1/14/2008 1:02:42 PM
Maybe this for minutes..........
=(B1-A1)*24*60

or just this, for days
=B1-A1

Vaya con Dios,
Chuck, CABGx3



"Jays" wrote:

> In Access I can use the DateDiff function to calculate the difference of 
> time between two times and it will also show negative time! in excel I have 
> only been able use if(time1<time2,1+time1-time2,time1-time2). Could I 
> somehow use automation to access that function... ? or is there an easieer 
> way in excel to show negative time!
> 
> Thanks 
> 
> 
> 
0
CLR (807)
1/14/2008 1:17:01 PM
What format do you want your difference in times to look like?

Rick


"Jays" <jayes33@hotmail.com> wrote in message 
news:OqbjDDoVIHA.484@TK2MSFTNGP06.phx.gbl...
> In Access I can use the DateDiff function to calculate the difference of 
> time between two times and it will also show negative time! in excel I 
> have only been able use if(time1<time2,1+time1-time2,time1-time2). Could I 
> somehow use automation to access that function... ? or is there an easieer 
> way in excel to show negative time!
>
> Thanks
> 

0
1/14/2008 3:35:01 PM
Thanks for your replies, I will keep that 1904 date system in mind, I think 
I will be using the =(b1-a1)*24*60.

"Jays" <jayes33@hotmail.com> wrote in message 
news:OqbjDDoVIHA.484@TK2MSFTNGP06.phx.gbl...
> In Access I can use the DateDiff function to calculate the difference of 
> time between two times and it will also show negative time! in excel I 
> have only been able use if(time1<time2,1+time1-time2,time1-time2). Could I 
> somehow use automation to access that function... ? or is there an easieer 
> way in excel to show negative time!
>
> Thanks
> 


0
jayes33 (7)
1/14/2008 5:20:46 PM
Reply:

Similar Artilces:

Why is my Access Queries take a long time to generate?
I used to do the query in less than 30 secs, but using the same method to do another query, it took more than a minutes to generate the table. Why is it so? There's only one practical way for us to know: Show us the SQL. Open the query in design view. Next go to View, SQL View and copy and past it here. Information on primary keys and relationships would be a nice touch too. Also any indexes. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Weak Access User" wrote: > I used to do the query i...

How Enter Amount of Time hh:mm and Sum Time?
How does one enter times in hours and minutes or hours, minutes an seconds? I want to enter amounts of time, not time of day. I see to keep getting stuck with time of day, and with some settings I'm no sure if it's time of day or amount of time. After I get this, how can I sum the daily amounts of time going acros a row to get a weekly total amount of time, and then sum the weekl totals in the weekly total column to get a monthly total amount o time? TomBrookly -- Message posted from http://www.ExcelForum.com Hi Format the cells as [hh]:mm from the Custom option. Enter ...

More problems displaying a negative time
Hi I have a chart which calculates segment time differences for a TV prgram which consists of short segments that make up the overall program. For example the program starts at 14:00:00 and must be finished by 16:58:00. I am inserting and removing segments inside the timefame and my formula in question is a simple one to display whether we are under or over the time allowed. "=ABS(H11-H12)" :where H11 is 16:58:00 and H12 is the sum of all the segment program durations. Problem is, this formala shows the result as an identical result for both negative and postive results. I wo...

US Time Zones and Opportunities
I have read a few postings about time zone questions and have a new one myself. We have offices in California, Denver and Washington DC. Our CEO is in CA with a sales team there and we have on in DC. Our CA based CEO has PST set in CRM as his time zone but an opportunity marked for January 1, 2009 shows as December 31, 2008 for anyone with PST. I understand that 01/01/2009 is 12:00AM, so when he looks at it with a time conversion he sees it as 12/31/2008 9PM. ] Why? I can see why you would want to make this change for a meeting request but this also happens for the estimated close date of an...

Auto date & time...
I have an excel file that I add data to everyday & would like to know how I can have 2 cells automatically fill in the date & time. Thanks- > I have an excel file that I add data to everyday & would like to know how I > can have 2 cells automatically fill in the date & time. > Thanks- > Do you mean you have 2 cells which need to display today's date & time. If so, copy this forumla in your cells: =NOW() If not, what date & time do you refer to? Hope the above helps. This updates the fields M1, N1 for any change in A1:H10 Private Sub Workshee...

Time response using ADSL connection
I have an access 2000 database create on a server for several users, now it is working on a network but I need to access this database using an ADSL connection (VPN) but the time response is too slow. What do I need to do ? Will someone please help me ? Thanks. Yeah, VPN isn't adequate for using with Access. Take a look at what Albert Kallal has to say at http://members.shaw.ca/AlbertKallal/Wan/Wans.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Humberto" <Humberto@discussions.microsoft.com> wrote in message news:CBD0DC69-...

How to validate time format from a text field?
The goal is to normalize data before converting text fields to datetime fields. There are start times and end times stored in text fields of a table. The problem is that any criteria used in a query causes errors when any function causes text to be converted to a datetime type, and the text field does not contain a valid date or time. I'm familiar with some of the validation functions such as isNull(), but I can't find anything like isTime(). How can I test existing data for valid time format? IsDate works on time as well. -- Doug Steele, Microsoft Acce...

On POS (not RMS) Adjusting Time Clock Defaults to Today's Date #2
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

Date and time #3
Can I have excel display the current date and time? Time is not tha important but date would be great -- Message posted from http://www.ExcelForum.com =TODAY() will update with every calculation or manually pressing F9 =NOW() date and time -- Regards, Peo Sjoblom "gsaenz >" <<gsaenz.103553@excelforum-nospam.com> wrote in message news:gsaenz.103553@excelforum-nospam.com... > Can I have excel display the current date and time? Time is not that > important but date would be great. > > > --- > Message posted from http://www.ExcelForum.com/ > ...

change format for time value
in column a i have duration time listed for minutes and seconds as m'ss". In column b i would like to show the same information as a but i would like to format the value as the total seconds. Ex if a1 = 1'30" then b1=90. What is the best way to do this? One way: B1: =A1 Format B1 as Format/Cells/Number/Custom [s] In article <6A3363E0-0215-439C-92A0-D714D0A63772@microsoft.com>, Qaspec <Qaspec@discussions.microsoft.com> wrote: > in column a i have duration time listed for minutes and seconds as m'ss". In > column b i would like to s...

publisher print is aligned half the time, misaligned the other
When printing from publisher, sometimes it lines up perfectly and other times it does not. I have gone in and reset all the settings. I make sure the stock is properly loaded in the printer. Does not matter. Sometimes it is right, sometimes not. I sure waste lots of business card stock. Publisher isn't at fault, your printer is. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Taterchip" <Taterchip@discussions.microsoft.com> wrote in message news:5860B75F-8532-401D-9645-8D17EF6C906C@microsoft.com... ...

Negative time should be allowed in Excel, eg time difference
If you want to subtract two time values, it shows error if negative. It would be an interesting feature e.g. for sports. Also, allow years before 1900 to be shown. ---------------- 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 message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.m...

Comparing Time in a Formula
I am trying to do some conditional formatting which requires me to compare two "time" values". Basically what I'm doing is changing the text color if the value of a cell, which is formatted as a time of day, if that time is greater than 9:00 PM. In the conditional format I tried "Use a Formula to determine which cells to Format" with the formula "=$B3>9:00 PM" and several varients of the same general idea. 90% of them returned errors, the other 10% made everything true... even if it wasn't. The idea is that if B3's value were af...

Exchange 2003 and pop3 delivery time
Lowest default is 15 minutes and I have a client who wants it set to 2 minutes. Any way this can be done? All help will be greatly appreciated. If you're using an SBS pop connector, no. If it isn't SBS, contact the POP connector manufacturer for support. A better option would be to ditch POP and host your own mail - then there's no delay at all, among other benefits. mark232@anywhere.com wrote: > Lowest default is 15 minutes and I have a client who wants > it set to 2 minutes. Any way this can be done? > > All help will be greatly appreciated. ...

need variable time axis available in XY chart for area chart #2
I am unable to use different X axis in the Area chart, which is available in the X Y charts. How do I use the X Y chart features of variable X axis to make the area chart? ...

ActiveX control
I have and application that was developed in Access 2000. The Date/Time Picker ActiveX control is on a form and works in Access 2000. Some users have new computers with Access 2003 installed When the application is opened using Access 2003, The control no longer displays the calender, it looks like a basic text box control. I've looked at the Access 2003 tool bar in the form design view and cannot find the Date/Time picker control. What can I do? Is there another control that is like the date/time picker for Access 2003? ****I know there are other functions and a calender contro...

WHAT IS THE FORMULA TO CALCULATE HOURS BETWEEN TWO TIMES
i want to calculate with the help of excel worksheet the hour worked by an employee if his in and out timings are given. for example if ones in time is say 9.30am and his out time is 6.42pm what is the total hours worked by him. to find out this what is the formula used? Just subtract and format as time. If you need hours in order to calculate pay, it's =(B1-A1)*24*C1 where start time is in A1, end time in B1, hourly rate in C1. On Mon, 27 Sep 2004 14:13:05 -0700, "babu" <babu@discussions.microsoft.com> wrote: >i want to calculate with the help of excel workshee...

Run-time Error 6 when Calculating Physical Inventory
Hi All, I have a customer who is getting run-time error '6' when calculating a Physical Inventory created using the 'Last Counted Date' option. RMS loads the items into the list but then when you press Calculate RMS starts to calculate but runs very slowly and then just fails with either run-time error 6 or Windows simply reboots. Two other physical inventory processes have worked out fine but this one refuses to work. This is the first time I have heard of this :-( Has anyone else seen this or hopefully any answers. The customer is running RMS SP3. Thanks, Jason. Have...

Change Format from Minutes(Time) to Number
Is there a way to convert the format of a cell in minutes to number format? For example, I am subtracting two TIME/DATE stamps and converting to Minutes with the result of 300. I want to use this 300 in calculations to find gallons per minute. When I use the number in a calculation, it using the Excel code for 300 minutes and not the absolute value (numerical value) of 300. Thank you. ...

Subtracting Time
Hello, I am subtracting sums of time and in some instances come across a negative number which is displayed with #################. What is a good formula to where if value is a negative number to display "-0" instead of "#"? For example: 31:26 - 49:43. Currently excel dispalys #s...would like to display "-0". Thanks. You are in the wrong newsgroup. This one is for FrontPage. Try one for Excel. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "Bob Oso" <Bob Oso@discussions.microsoft.com...

How to make Row_DataBound in GridView work all the time?
Hello Community Using ASP.NET and C# with Sql Server in the back end I have a GridView that uses Row_DataBound event on a web page. When one of the columns contains a letter in the database such as “N” or “Y” that column in the GridView shows “No” or “Yes” using Row_DataBound. This works fine initially but after modifying a row in the database the “N” or “Y” doesn’t show the “No” or “Yes” in the GridView when the page comes back but instead still shows the “N” or “Y” in the GridView that is literally in the database until I add another record, then it will go back ...

Time inputted as minutes and seconds Excel
When inputting time that is in minutes and seconds I have to use a decimal and a zero. i.e. mm:ss.o. Can I reformat so I can input as mm:ss -- EDba No. Formatting has no effect on how XL parses the entry (unless you set the format to Text, in which case you won't get an XL time). You could use an event macro to divide your entry by 60. Here's one way. Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub ...

Scope Variables and SSIS Sequence Containers
Is it possible to use variables that are specific to a sequence container only ? Variables are available in the Control Flow and Data Flow Task areas but I don't see any variables which are available with scope limited to Sequence containers. I am using SSIS 2005. Thank you, @jm27102 Click inside the container you wish to create the variable in and it will be scoped to that container. As an alternative, using the Variables Window you can select a container from the Scope Dropdown box. Regards, "jm27102" <jm27102@discussions.microsoft.com> wrote i...

simple time sheet issue
Hello, I posted a question yesterday, but i cannot find my question- or any answer, today. So here we go again- I have created a breakdown table for my companies roster. I have a simple formula in the spreadsheet that tells me how many hours of a shift was on the day rate (0530-1830) (24hr time), and how much is on the night rate (1830-0530) ( security guards). for example I need the cell that has the formula =SUM(G7-1830) (G7 being a time between 0530 and 1830), to display zero, until i type a figure into G7. Please help! P.S- I'm not fluent with Excel so laymans terms would be app...

I need to create a date/time search query but i cant figure it out!
Hi, I have a time-slot grid that I want to be able to click on and have access find out if there are any records based around that slot.. so basically, I have a varDate and a varTime and I need to use a query to query against my databases StartTime, EndTime, and AppDate variables.. in psuedocode i need to find the result of WHERE varDate = AppDate AND (StartTime <= varTime AND EndTime >= varTime) Ive never done a search using dates before. I was wondering if the formatting of the dates is important? I did do a basic query that seem to fail when just searching for dates.. the...