calculating time/dates differences

I'm looking for a formula that will allow me to calculate the difference 
between the time and date of one day and the next.  I also need to be able 
subtract out the time between 17:00 ahd 08:30 as well as weekend days and 
holidays.  Example 6 June 16:51 to 7 June 9:51
0
6/22/2005 9:21:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
362 Views

Similar Articles

[PageSpeed] 49

Here's some formulas and information that might help:
http://www.officearticles.com/excel/date_formulas_and_functions_in_microsoft_excel.htm
The most important part is the formatting of the cell that contains the
formula.

Also, when you subtract dates/time, the answer is provided in DAYS. So if
you want the number of hours, you have to add /24 to the end of your
formula.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"fvglassman" <fvglassman@discussions.microsoft.com> wrote in message
news:0FB76DAE-5690-4DC4-92D0-49B6CDE55731@microsoft.com...
> I'm looking for a formula that will allow me to calculate the difference
> between the time and date of one day and the next.  I also need to be able
> subtract out the time between 17:00 ahd 08:30 as well as weekend days and
> holidays.  Example 6 June 16:51 to 7 June 9:51


0
ng1 (1444)
6/22/2005 9:28:12 PM
"Anne Troy" <ng@officearticles.com> wrote in message
news:562ac$42b9d7e0$97c5108d$8584@allthenewsgroups.com...

> Also, when you subtract dates/time, the answer is provided in DAYS. So if
> you want the number of hours, you have to add /24 to the end of your
> formula.

I think you mean *24 Anne :-)


0
bob.phillips1 (6510)
6/22/2005 10:04:55 PM
Uh. Thanks, Bob! :)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uH3YtZ3dFHA.3712@TK2MSFTNGP12.phx.gbl...
>
> "Anne Troy" <ng@officearticles.com> wrote in message
> news:562ac$42b9d7e0$97c5108d$8584@allthenewsgroups.com...
>
> > Also, when you subtract dates/time, the answer is provided in DAYS. So
if
> > you want the number of hours, you have to add /24 to the end of your
> > formula.
>
> I think you mean *24 Anne :-)
>
>


0
ng1 (1444)
6/22/2005 10:10:59 PM
Reply:

Similar Artilces:

What is the max limit exchange 2003 can send at 1 time?
I seem to be getting cut off at 200 messages through my gfifaxmaker program and I was wondering where I could go to find this setting and change it to a higher limit. microsoft wrote: > I seem to be getting cut off at 200 messages through my gfifaxmaker program > and I was wondering where I could go to find this setting and change it to a > higher limit. That sounds to me like a good thing. --JM Well its not a good thing, I use to be able to send out 2000 faxes from exchange 5.5 and now my distribution list has to be cut into 10 groups instead of having them in one. I ju...

Problem with Date Manipulation
Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT---> I dont want the weekends to count as days! Material arrives every weekday & exits the...

How to determine difference?
If I have a cell that is 100 and another cell in which I want to demonstrate the difference - how do I set this up? The problem is that at the moment if I use a simple a1-b1 I get a negative number if b is larger. What I want is it to indicate the change as a +/-. So if A is 100 and B is 105, I would like to cell to indicate 5. If B is 95 - then -5 - does this make sense? Any help is MUCH appreciated. one way: =B1-A1 In article <10v0ggoi91s4c57@corp.supernews.com>, "Fionavar" <fionavarXXX@perlucidus.XXX.net> wrote: > If I have a cell that is 100 and an...

"Configuring Microsoft Office Basic 2007" every time I start
I added memory to my PC and Office 2003 started to misbehave (and it could not find a CAB file that was on the CD) so I removed it and upgraded to Office 2007. Now every time I start my PC it spends time configuring itself. I might add iTunes is the same. It means starting the PC is an even more laborious process than usual. Oh, and I have some annoying update to XML something or other that does not install either, and does not go away. It is ironic that the extra memory was to improve perforamnce. Every step forwards involves two steps back. Apart from swapping to App...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

Planning Lead time-Item Vendors Maintenance window
Previously used manufacturing and MRP in v9 and the Planning Lead time on the Item Vendors Maintenance window (bottom left corner), we used to enter 7 for the number of days in 1 week. MRP then suggested to place PO's using these 7 days as one week. Now when we enter 35 for example ( V9 calculated 5 weeks for placing the PO) Now since V10 it looks like it is calculating 7 weeks lead time (35 days divided by 5 days) Did something change with V10 on this field and how MRP uses it with the Request Resolution window? thanks! -- Doug The system now looks at calendars and can conside...

UTC time to Central Time (US)
Hello. I need to convert UTC time to US Central Time Zone. The date is in one column and the time is in another below. A | B ---------------------------------- Date | Time ---------------------------------- 2005-10-24 | 12:15:00 I want to convert the Date and Time columns in UTC to a single Date and Time column in US Central Time. Thanks for any advice Assuming UTC is the same as GMT then the difference is 6 hours STD, so if the time is 06:00 AM it is 12:00 PM GMT, thus add 6 hours and since the y are all numbers just add =A1+B1+(6...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

List of Differences Between Outlook 2000 and Outlook 2003
I need to spend 45 minutes introducing Outlook 2003 to my Outlook 2000 users. Can anyone provide a site with a succinct list (screenshots would help) of differences between Outlook 2000 and Outlook 2003? Many thanks. Tom Are you looking for screenshots of Outlook 2003 or ones comparing OL2000 and 2003? Wouldn't you be a more convincing teacher if used both and took the screenshots yourself? This would help you learn and understand the products better. (Vmware or virtual pc make this really easy and you could show them the differences live. :)) -- Diane Poremsky [MVP - Outlook] ...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

partially turning off auto calculate?
Any way to NOT auto caluculate 1 particular formula, while leaving the rest of excel to auto calculate? In particular, I have an SQL query I would prefer to run manually, while not interferring with the rest of my workbook(s) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Calculation is an all or nothing setting. You can't prevent calculation of specific ranges. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com ...

Outlook hangs ,7th time :-(
Hi all, In our company we are using Exchange 2000 as mail server, and outlook 2002 sp2 as email client. Each client is configured to download its emails to a local pst file. We are facing the following issue : If you add a rule to move messages from inbox to a particular folder, during send/receive and just after receiving 3-5 messages (and these messages are moved to their folders) the outlook stops responding. If you run outlook with /safe parameter it will not hang! If any one could advise I would be gratefull. Thanks, Osama. ...

converting dates
Hi All I have a couple of excel problems to do with dates (Excel XP/2002). I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365. Secondly does anyone know a formula that sorts out leap years (see above). Any help, suggestions or tutorial links greatly appreciated Rexmann As long as you assume 1 year is always 365 days =INT(A1/365)&" year(s) "&MOD(A1,365)&&q...

Can I display the current date in a text box?
I know how to display the current date in a cell, but can I display it in a text box? And how would I do that? You would have to have some code to load it, such as Textbox1.Text = Format(Date,"dd mmm yyyy") or link the texbox to a cell with the formula =TODAY() -- HTH RP (remove nothere from the email address if mailing direct) "stephiebrady" <stephiebrady@discussions.microsoft.com> wrote in message news:C78C4C78-C12C-4A8F-9121-E377ACAE3B5B@microsoft.com... > I know how to display the current date in a cell, but can I display it in a > text box? And ...

Adding time
I want to add time,like a sum. 12:00+12:00+12:00=36:00? I keep getting a basic time like 12:00. Could you please supply a formula. Thank you Hi In the cell with the formula, Format>Cells>Number>Custom [h]:mm Putting the [ ] brackets around h allows it to sum past 24 hours. Regards Roger Govier trucker wrote: > I want to add time,like a sum. > > 12:00+12:00+12:00=36:00? > > I keep getting a basic time like 12:00. Could you please supply a formula. > > Thank you > ...

keyboard events for different CWnds
We know that BOOL CTestDialog::PreTranslateMessage(MSG *pMsg) can be used to capture keyboard inputs for some dialog class CTestDialog and in general any window. But consider this.. If I have a MDI application and therein certain Key strokes 'belong' to the mainframe and others to the child frame and perhaps certain others to some other pane or whatever, what is the best way to implement this? For instance, Ctrl+S saves a file in many apps (message goes to MainFrame) and Ctrl+C copies content in the child window (message goes to ChildFrame). What If I want customized key presses for...

What is the difference
What is the difference between IIF condition and IF condition ? "Kutty" <Kutty@discussions.microsoft.com> wrote in message news:46A1B86F-2002-4548-916D-01E0B3F2C602@microsoft.com... > What is the difference between IIF condition and IF condition ? IIf() is a function that can be used directly in a query or control source. If is used in VBA code only. Unless you are thinking of the If() function from Excel in which case that does not apply to Access at all. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com On Feb ...

Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & DATEDIF(C6,NOW(),"md") & " D" I'd like a formula that can produce the same format (years, months, days) between now and a future date. Any ideas? Thanks in advance, Bart Hi Bart Try this with the date in A2 =IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...

Time Entry
A simple problem I would appreciate your help with. I have a work sheet with several cells where time is to be entered in the format hh:mm. Unfortunately a lot of users are enetering it in the format hh.mm. Is there any way I can automatically convert to the right format, or not accept the (.) but only (:). Thanks for your help. Private Sub Worksheet_Change(ByVal Target As Range) If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then MsgBox "invalid entry" End If End Sub Enter the above code in the sheets module Mangesh -- mangesh_yadav -------------------...

Modify Calculation
When a user enters data into a cell, he has to tab out of it before he can push the "calculate" button. Is there any way to change this so that the calculation can be performed while the cell is still selected? I appreciate any help. Change Tools>Options>Edit>Move selection after enter, and use Enter to complete entry rather than Tab. This prevents the selection from moving to another cell. Note that calculation can never be done while the cell is being edited. "Sisilla" <sookdeoss@bowater.com> wrote in message news:0b4e01c34b9b$d59ebcf0$a301280a@p...

Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for records that came in between two dates. For instance, I would like to be able to pull all records input in CRM between March 1 and March 15. In 3.0 you can only query specific dates like "Last X days," "Last X Months," "On," "On or After," etc., but you cannot search the date fields between two dates. The functionality was available in 1.2 but is not available in 3.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

Sort search results by date
It would be nice if i could sort my search results in this forum by date. I would like to see my most recent questions at the top. -- Sheri Salomone THANKS! ---------------- 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/Businesssolutions/Community/NewsGroup...

Run-time error 438?
Hello, I am trying to create an edit control for my form. I lock my textboxes in properties and then I created a command button with an event on onclick. My codes are working for most of them expect a few. When I click the button it gives me Run-time error 438. My code is based liked this. Me![Control].Locked=False Private Sub Edit_Record_Click() Me![Drago ID].Locked = False Me![Drago Claim Number].Locked = False Me![Dealer Claim Number].Locked = False Me![Dealer].Locked = False Me![Store].Locked = False Me![Machine Acres].Locked = False Me![Part Fail Date].Locked = False - NOT WORKING ...

vbscript insert into access 2003 database with two different table
I am trying to insert data collected by WMI. Here is the script On Error Resume Next Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const ForReading = 1 'Create FSO Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an environment for the script to work Set wshshell = WScript.CreateObject("WScript.Shell") 'Connection to the database Set cnn = CreateObject("ADODB.Connection") 'Connection to a Recordset Set objRecordSet = CreateObject("ADODB.Recordset") 'Opens the Database ...