count between dates help (NETWORKDAYS)

Hello big brains



I am trying to find the days and hours between 2 dates

2 columns of dates formated in M/D/YYYY hh:mm

4/23/2004 15:53 and 4/27/2004 10:43

I don't want to count weekends so I used NETWORKDAYS which works to a
degree.. giving me 3 (which is days) but it's ignoring the time. I
need it to be specific and have it show days and hours difference. I
am suprised that it is ignoring it.

Unfortunately I am kind of stuck with the dat being in this format.
Any help would be appreciated.

thanks


0
nospam7515 (2084)
5/15/2004 12:24:35 AM
excel 39879 articles. 2 followers. Follow

3 Replies
458 Views

Similar Articles

[PageSpeed] 59

Maybe this can give you some ideas

http://www.cpearson.com/excel/DateTimeWS.htm

-- 

Regards,

Peo Sjoblom

"Montys Python {wink wink}" <nospam@nospam.com> wrote in message
news:13oaa05jmg42cn0tqej8drf6d0du3bj7if@4ax.com...
> Hello big brains
>
>
>
> I am trying to find the days and hours between 2 dates
>
> 2 columns of dates formated in M/D/YYYY hh:mm
>
> 4/23/2004 15:53 and 4/27/2004 10:43
>
> I don't want to count weekends so I used NETWORKDAYS which works to a
> degree.. giving me 3 (which is days) but it's ignoring the time. I
> need it to be specific and have it show days and hours difference. I
> am suprised that it is ignoring it.
>
> Unfortunately I am kind of stuck with the dat being in this format.
> Any help would be appreciated.
>
> thanks
>
>


0
terre081 (3244)
5/15/2004 1:02:07 AM
thanks for the pointer

It looks promising but I am having difficulties making it work.

I'm assuming that in that formula StartDT and EndDT can be substituted
for the Cells that contain the dates...where as the example uses a
fixed date. Maybe thats the problem....



On Fri, 14 May 2004 21:02:07 -0400, "Peo Sjoblom" <terre08@mvps.org>
wrote:

>Maybe this can give you some ideas
>
>http://www.cpearson.com/excel/DateTimeWS.htm

0
nospam7515 (2084)
5/15/2004 12:48:07 PM


Here is what I have

Cell B118                       Cell C118
4/23/2004 15:53          4/27/2004 10:43


With the rest of column B & C populated with dates/times

this formula results in a formular error

=IF(AND(INT(B118)=INT(C118),NOT(ISNA(MATCH(INT(B118),A127,0)))),"0
days 0 hours", IF(INT(B118)=INT(C118),"0 days " &
ROUND(24*(C118-B118),2)&"hours",MAX(NETWORKDAYS(B118+1,C118-1,HolidayList),0)+INT(24*(((C118-INT(C118))-(B118-INT(B118)))+(20:00-9:00))/(24*(20:00-9:00)))&"
days
"&MOD(ROUND(((24*(C118-INT(C118)))-24*9:00)+(24*20:00-(24*(B118-INT(B118)))),2),ROUND((24*(20:00-9:00)),2))&"
hours "))

I don't have any Holidays so I pointed that to a blank cell.

I'm a bit lost on all this...

thanks for anything you can offer





On Sat, 15 May 2004 12:48:07 GMT, Montys Python {wink wink}
<nospam@nospam.com> wrote:

>thanks for the pointer
>
>It looks promising but I am having difficulties making it work.
>
>I'm assuming that in that formula StartDT and EndDT can be substituted
>for the Cells that contain the dates...where as the example uses a
>fixed date. Maybe thats the problem....
>
>
>
>On Fri, 14 May 2004 21:02:07 -0400, "Peo Sjoblom" <terre08@mvps.org>
>wrote:
>
>>Maybe this can give you some ideas
>>
>>http://www.cpearson.com/excel/DateTimeWS.htm

0
nospam7515 (2084)
5/15/2004 1:07:00 PM
Reply:

Similar Artilces:

Spin Box with Dates and Lookup
Hi, I'm trying to design a spin box function which pulls in data from monthly tabs, the month being determined by a spin box. I have monthly tabs Jan-10 to Dec-10 all containing an identical table with different data in. The user needs to be able to change the month of lookup using a spin box function but I cant get it to work properly. Thanks in anticipation. Hi, You are not very clear about your question. Anyways, try this Right click on the spin control box which you have drawn ad give the lower and upper limits are 1 and 12 respectively. In the cell link box, ...

Item's Last Sale Date
Hi there I'm creating a smartlist report that will display the item details but not sure which inventory table I should use to get the Last Sale date of an item. Any ideas? thanks. You can try this query to fetch the item number with the highest date of sale (or last sale date): SELECT ITEMNMBR, DOCDATE FROM IV10201 GROUP BY DOCDATE, ITEMNMBR HAVING DOCDATE =3D MAX(DOCDATE) AND ITEMNMBR =3D 'xxx' You can change 'xxx' with your item number. I've not tested this fully so you may want to run this in your environment and make sure that this query will work for every ...

Count Based on Comparison with Two Fields
I recently learned that I can count the number of items in one column that match items in another column by using: =COUNT(MATCH( 'Sheet1'!G2:G15001, 'Sheet2'!A2:A15, 0)) However, I now need to count records on one sheet in which TWO fields match items in both of two separate columns (i.e., a comparison of two columns to two columns and a count of records in the first that have fields matching elements in the second) . My attempt to use the above statement with an AND failed. Can someone explain how to do this? Hi I would suggest that you post a before a...

Specific Age Query for a Date Range
Table = Personal Field = Birthdate I need to create a query to show who is of a certain age for January 1, 2008 to December 31, 2008. For example, I need to know who will be 50, 60, 65, 70 and 75. I would like to create the query so that when I click the query to open it, I have to input the age. I've done other queries to show who is of a specific age as of today: AgeYears: DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), with criteria of [Age in Years] ... but I cannot figure out how to pull a report...

Help Microsoft Office 2003
After now using Microsoft Office 2003 for about 3 months now, have suddenly noticed that my email messages have just started showing strange symbols for certain punctuation. This does not show as I type... but the recipent sees these symbols when the message is received. I can see it when they reply back. The strange symbols show for the apostrophe and sometimes for the exclaimation. I do not recognize the symbols. I do not have caps lock on, do have number lock on. None of the symbols show on my keys. Help?! Thanks! Kim it's caused by character encoding. What email client and...

.pst folder help
How do I put a .pst folder in My Documents for constant up to date copying? Mine seems to be in a folder called Office Data File which I cannot open but do not see any pst file...can someone kindly help me out here? Thanks! Right click the pst in outlook and select properties and then click Advanced You will see the location of the file. Depending on your view settings it may be hidden. Close the pst, (right click it, close) Move the file to the location you desire. Open Outlook select File - Open - Outlook Data File Browse to the file and select it. OK Done Regards Mark Dormer &quo...

Calendar dates in all users outlook
G'Day All, I am running E2K and all users are running Outlook XP on their desktops. Without visiting all users computers how can I update all users' Outlook Calendar to show the days/dates the office will be closed? Thanks I have the same problem. The only workaround I could find was to send out an email meeting invitation to everyone and instruct them to click accept (which they eagerly do if it's a holiday). If you find a better way let me know! Chris ...

Routing Engine not starting, please help ASAP
Exchange and Windows 2003 I cannot get the Exchange Routing engine to start up. Upon startup I get an event ID 7023 telling me that the path is not found. Everything else works and starts up just fine, people can open mailboxes, but cannot send/receive emails. I read online that it sounds like the metabase.bin file is corrupted. I uninstalled/reinstalled IIS, reinstalled Exchange 2003, and have reinstalled Exchange 2003 SP1, everything works except that I still cannot get the routing engine to kick back on. Any suggestions! Thanks! Was this an OEM preinstall? I recently bought a ser...

Help with macro to choose printer
Hi, I am new to macros in Excel and this is what I did to print a worksheet: ------------------------------------------------------------------------ Range("A1:W35").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$W$35" Selection.PrintOut From:=1, To:=1, Copies:=1, Collate:=True End Sub ------------------------------------------------------------------------ Works great except that I want to select the printer to use and the macro won't stop at the point to choose printer. Any way to work around this? Any help is greatly appreciated. Emilio Emilio,this wil...

Help with an expression to calculate a profit from one of three fi
Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as &quo...

Help with Lookup #2
I have an excel worksheet that has approximately 20,000+ records. I would like to classify these recorded based on two columns. The first column's name is "YB Description" and the other is "Sold Date". The YB column mainly contains 4 digit numerical values, with approximately 300-400 records that are text, ie. "classified", "not verified", "public use". The Sold Date column is pretty self-explanatory, all the records are dates with the following formatting - M/D/Y. What I would like to do is create a third column called YB/Sold that classi...

Group By Flag Status and then Received date
I want to group first by flag status and then by Received date, but have the same collapsable headers available in the Received that you get if you group first by Received date. It looks like it may not be possible, but thought I would post to see. Thanks! Following is the example. So: Group: Flag Color Unflagged (Then emails by date with this flag color grouped as the following) Date: Today Date: Yesterday Etc. Instead of Group: Flag Color Unflagged (Where the groups by date are specific including the time). Date: 6/9/2006 4:18 PM Date: 6/9/2006 4:12 PM ...

Help with ACT! to CRM migration
Hi to anyone that can help. I am trying to do a trial data migration from our ACT! database to MSCRM to see what it will look like. I have installed the migration framework and appear to have CRM running fine on a server in our network (no errors on install and all windows appear ok). Since I do not have a strong database background I was wondering if someone could confirm what I believe is MS's way of migrating my data accross. I have exported the data from ACT! to a text file. I imported this into excel and repleaced the Free/busy data with a unique number for each record. I the...

Help with Links to Files
Hello, I have a file in Word 97-2003 compatibility mode. When I select the Office Button / Prepare I see an option to Edit Links to Files. If I convert the file to 2007 format the option for Edit Links to Files is no longer there. Where did they go? thanks, mitch This is strange. The Edit Links to Files option should be available even if you save in Word 2007 format. Are you sure you didn't inadvertently break the link to the automation object? You could press Alt+F9 to see that the field code for the object is still intact. (Note that you can only see the field co...

Need help backing up OL2002 items (or folders)
HELP says: "If you want to create a folder to which you can drag items for safekeeping or to distribute to another computer. Create a new data file (.pst) and drag items to it. If you used the default name for the folder when you created the data file, the folder will be called Personal Folders . In the Folder List (Folder List: Displays the folders available in your mailbox. To view subfolders, click the plus sign (+) next to the folder. If the Folder List is not visible, on the View menu, click Folder List.), drag any item from your current folders to the new folder. Press CTRL...

Need to sort dates before 1900 in proper order
I need to sort dates before 1900 in proper order -- ones after 1900 are all fine but any dates like 03/29/1865 will always sort by month and day but NEVER the year except in the month area. Don't know if I am making sense -- but will be like: 03/29/1865 03/29/1866 03/29/1873 04/01/1863 04/01/1868 etc. Thanks, Judy M. I can provide a chunky workaround, until someone provides a better answer: Assuming your 5 sample dates are in cells A1 ~ A5, and that single digit months and days are always expressed with a leading zero: In cell B1, enter this formula: =VALUE(MID(A1,1,2)) In C1, ente...

Date and Time Picker Control problem
Hi, I've constructed a tool that employs multiple Userforms and several incidences of Date and Time Picker Controls (version 6.0). Amongst the group that use this tool there is one individual who gets the following message. "Could not load an object because it is not available on this machine". I've isolated this object to the Date Picker. I've checked Excel and Service pack version and they are the same as mine yet this individuals standard load does not seem to include this particular control. Is there any way of installing this control for the individual ...

password
Hi I have problem with Microsoft Outlook XP. Very often Outlook won't remember the password. Thx I am having a similar problem. Using Outlook 2002 (XP). I have several POP3 accounts, and Outlook requires the passwords every time. I've tried everything, and it still forgets them. Thanks to anyone that can help. >-----Original Message----- >Hi >I have problem with Microsoft Outlook XP. Very often >Outlook won't remember the password. > >Thx >. > Earlier reply had the incorrect e-mail address. This address is correct. Jim V >-----Original Mess...

Add rows on worksheet by date?
I've been asked to create a spreadsheet that will track vehicle miles by route. The drivers will note the odometer when they start the route and the odometer when they return, so we can log that across a row: Vehicle - Route - Odo Start - Odo End - Calc Route Miles They also want miles by vehicel by day. Since a vehicle can travel more than one route in a day, is there an easy way to do this? My original thought was to have input through validation drop-downs to choose teh vehicle and the route - now I'm thinking of using a Form, and the Enter Data button will automatically sort by...

Help w/ Accounts
Hi, I have Outlook 2003, I used to have four email accounts in Outlook. One of those accounts was set up to check the email but leave the original in the server. It was working fine until all of a sudden it started downloading the same email twice and deleting it from the server. I deleted the account from Outlook, but it is still downloading the email from that account. I went to Email accounts and I don't see that particular email account listed there. Does anyone have any idea how to get rid of this "invisible" email account that does not show up in the email accounts an...

Need help writing an expression to find a Quarterbacks Passer Rati
I'm trying (without success) to write a formula to use in a table to figure the NFL quarterbacks passer rating. Here are the components, and I believe my errors are the maximum and minimum and not knowing how to use them in the formula: C= Comp/Att*100-30/20 Y= YDS/Att-3*.25 T= TD/Att*20 I= 2.375-INT/Att*25 The four components are then added and divided by 6 and multiplied by 100, so the formula for passer rating is given as: (max(min(C,2.375),0)+max(min(Y,2.375),0)+max(min(T,2.375),0)+max(min(I,2.375),0)*100/6 I have played for 3 days with this monster formula and have not figured ...

Re-set a date to the first of the month
Hello, I need to re-set whatever date is entered in a cell to the first of that month. For example if someone enters 9/11/2008, I need it to be re-set to 9/1/2008. Is this possible, and if so, can you please tell me how to do it?? thanks in advance! You could use a formula in another cell: =date(year(a1),month(a1),1) or =a1-day(a1)+1 Then copy|paste special|values over the original range (a column???) OS wrote: > > Hello, > > I need to re-set whatever date is entered in a cell to the first of > that month. For example if someone enters 9/11/2008, I need it to be > re...

Can I expand the column count in excel 2002. Or limited to 256?
I am exporting data from a crystal report into excel (data only) format, but I am getting cut off at column IV (256). Is there any way to increase the column count in my worksheet? No. -- Kind regards, Niek Otten Microsoft MVP - Excel -- Kind regards, Niek Otten Microsoft MVP - Excel "KWE39" <KWE39@discussions.microsoft.com> wrote in message news:27E98319-1C1F-4A6A-A1F6-32E328BD9D1F@microsoft.com... >I am exporting data from a crystal report into excel (data only) format, >but > I am getting cut off at column IV (256). Is there any way to increase the &...

Need help getting data from Word to Excel
I'm novice level with Word and Excel, so excuse what may be a basic question/problem that I've got. I asked an employee to enter lots of data into Excel. The data is company name, address, contact name, and phone number. I requested that each type of data be entered into different columns. Instead, the employee used MS Word 2003. To compound the issue, the employee did not use commas, tabs, or any other delimiter or formatting method that might allow me easily to get the data over into Excel (by *easily* I mean using a technique that I know ; ) The data in Word simply is s...

convert numbers to dates #2
thanks to you all now sorted -- jimbo693 ------------------------------------------------------------------------ jimbo693's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14215 View this thread: http://www.excelforum.com/showthread.php?threadid=263696 ...