#### Help with Date/Time Function

```I am working with date/time functions trying to figure out how many
hours were spent doing a job.  I have a start time and a finish time.

But I need to subtract out any time between five pm and six am and
weekends from the times.  These would be times that no one was actually

working on the job.  For example I have

4/5/2005 18:23  4/6/2005 17:04

Could someone give me a clue as to how this might be done?

Thanks,
Steve Monczka
smonczka@hotmail.com

```
 0
smonczka (53)
5/29/2005 2:07:53 PM
excel 39879 articles. 2 followers.

2 Replies
501 Views

Similar Articles

[PageSpeed] 43

```It's not a trivial thing, here's an example

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

or here

http://tinyurl.com/cbphn

--
Regards,

Peo Sjoblom

"Smonczka" <smonczka@hotmail.com> wrote in message
>
> I am working with date/time functions trying to figure out how many
> hours were spent doing a job.  I have a start time and a finish time.
>
> But I need to subtract out any time between five pm and six am and
> weekends from the times.  These would be times that no one was actually
>
> working on the job.  For example I have
>
> 4/5/2005 18:23  4/6/2005 17:04
>
> Could someone give me a clue as to how this might be done?
>
> Thanks,
> Steve Monczka
> smonczka@hotmail.com
>

```
 0
terre081 (3244)
5/29/2005 4:20:17 PM
```Thank you Peo, that did what I needed.

```
 0
smonczka (53)
5/30/2005 5:07:56 PM

Similar Artilces:

Help with formula #31
I'm sure there is a way to modify this formula but have no idea.I'd like for it to do this: =IF(A30="","",SUM(C3:C25)*ROUNDUP(Worksheet2!G3/G1,0)) which is fine. But if Worksheet2!G3 has more than whats in G1 then MINUS Sum(C3:C12) to the above formula. Example: Worksheet2!G3 has the total Qty. Sheet1 has a number in it (25) For every 25 this formula is dividing Worksheet2!G3 by 25. I want it to divide only once, then Do formula Minus Sum(C3:C12) as many times as G1 will go into it. Thanks in advance!!! ...

Help filling in form from 2 different tables
I have two tables, CustomerData and EmployeeData. CustomerData has all the address, phn, name, empid# of last employee to help em and custid (primary key) and employeedata has name addr phn and empid (primarykey) I have a combo box that pulls up custdata (by Custid sorting by Lastname & ", " & Firstname) I wanna do an Afterupdate event to fill out the following textboxes (based on the Custid it fills out the Lastname, Firstname, Address, Phnnumber) and then based on the Empid of the last Employee that helped em it will fill out the textboxes witht he first and last na...

OUTLOOK 97 Resetting time span for email arrival
I am trying to avoid going into TOOLS "check for new mail" and have OUTLOOK continuously display new emails. What can I do? I did not see anything under "Options" that gives me a choice for resetting. ...

How do I indicate a empty or blank cell in an IF function?
I am trying to do a simple IF function but don't know how to indicate if a cell is empty or blank then I want a certain text value returned. What I am trying is: =IF('Sheet 1'!A15=IS NULL,"NO","YES") What I want is if the cell indicated on an other sheet is empty or blank then the word NO to appear in the formula cell and if it does have text then YES to appear. How can I do this? Hope I made myself clear enough. I couldn't figure out how to ask help the right way to get the correct answer. Thanks for all of your help! Never Mind. I seem to ha...

hOW TO USE eXCEL FUNCTION
=IF(AND(M26<=7501,N26="A"),M26*G12,IF(AND(M26<=7500,N26="B"),M26*F12),IF(AND(M26<=7500,N26="C"),M26*E12),0) SO THAT I AM ABLE TO UNDERSTAND IF SALARY OF EMPLOYEE LESS THEN 7500 AND GRADE IS A THEN 10% OR GARDE IS B THEN 8% OR GRADE IS C THEN 6% OR NOINCREMENT Hi, I think this is what you want =IF(AND(M26<7500,N26="A"),M26*G12,IF(AND(M26<7500,N26="B"),M26*F12,IF(AND(M26<7500,N26="C"),M26*E12,0))) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest a...

Excel 2003
Hi all Excel Gurus, I need help. I have an excel workbook with a sheet called template and i want to create a button to copy a selection of data which is B9:B34 and i need it to paste the information to another excel workbook with a sheet called data which will be A:AA and add each row when pressed. When the data has been copied over i need it to close the data sheet. Can someone help me please? When you copy a range of cells you only have to specify in the destination l;ocation the 1st cell of the range like I did below. Private Sub CommandButton1_Click() FName ...

Hi everybody, what can be the reason why for some crm users in our domain, the incoming replies originating from crm contacts are populated as an activity but for some others they are not (eventhough replies come to their outlook inbox folder). Am I missing something in terms of user rights for that users? Any help will be appreciated! Thanks Have all the users had the CRMEMailEnable setting added to their profile in AD? -- Matt Parks MVP - Microsoft CRM "Isil" <Isil@discussions.microsoft.com> wrote in message news:9A86BDB4-F184-45D0-9A38-3C1D1470B88B@microsoft.com....

I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? I am very basic when it comes to VBA and any help would be greatly appreciated. ...

Calculating diff between date/ time only between certain hours 02-24-10
I am trying to calculate the difference in hours between a date/time range but only the hours in a certain range. 1/27/10 4:49 1/27/10 11:32 I am trying to find the diff in hours only between 6:00 and 23:59, any hours that fall outside of that time frame should not be counted. Each calculation will have different dates so if there is a way to do this without specifying the date that would be great! GMv1 - If your start date is in A4 and your end date is in B4, then you can do this: =MOD(B4,1)-IF(MOD(A4,1)<0.25,0.25,MOD(A4,1)) This works becase date/times are stor...

How do I format the Auto Date font that is inserted
Sorry to bother with such a minor thing.... When creating a page that is captured from the web, a date and time is already inserted at the top of the page... which is great. The only problem is that it is so tiny and light colored font that it is hard to see. Is there a way to change that font? Thank you. Nancy PS- I just started using Onenote and it is a great program! I just love it. Perfect for what I need. Never mind... I see the answer in a previous post. Sorry, I thought I had read all the previous post headers. Nancy "Nancy" <weewee37@hotmail.c...

help in publisher and can't see the page tab on the bottom
I can't view my page tabs so I can't move btw pages in my publisher file- i know they are there for i see it in Print Preview but how do I get the tool bar on the bottom back? Mercy0986 wrote: > I can't view my page tabs so I can't move btw pages in my publisher > file- i know they are there for i see it in Print Preview but how do > I get the tool bar on the bottom back? ============================ For Publisher 2003 or earlier: Go to.....View / Status Bar For Publisher 2007...have a look at the following article: Where is the Page Sorter (status bar) in Publish...

how I can insert date picker in excell template?
Maybe one of these will help: http://www.rondebruin.nl/calendar.htm http://www.fontstuff.com/vba/vbatut07.htm Regards Rowan ...

Newsgroups in Outlook Express
Greetings! I am new to using Outlook Express in WinXP. In the newsgroups I can view image files that are not "yEnc" but cannot view the ones that are "yEnc". Could someone please tell me how to configure Outlook Express v6 to view such files? Thanks in advance, SC "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -- Benjamin Franklin I don't think OE supports yEnc - see insideoe.tomsterdam.com to verify. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coa...

Color rows based on date in a cell?
Programming question? Hopefully so. Excel novice here I would like to color a row yellow at a certain date, then red at another. This is for a project and the yellow and red correspond to due dates. Say at 12/11/2009 the row is yellow. At 12/19/2009 it is red. I would also like to color a row (some color) when a 100% is reached in the "completed %) cell. Help is appreciated! Ignore, as this is now in General Excel forum,, "tbo" wrote: > Programming question? Hopefully so. > Excel novice here > > I would like to color a row yellow at a certai...

constant restart of msexcimc service help..
I have been running across very slow opening of sub containers within my mailboxes. Sometimes to the point where the requesting information window pops up and sits for a long time and never connects... I found that re- applying sp4 sometimes works.. Yet I found that a quick restart of the \msexcimc.exe service is all that is need to make the connect fast and eliminate the non connect.. any ideas what is going on here? any ideas how to patch this?? I hate having to do this once a day.. =( Thanks... "ROb B" <anonymous@discussions.microsoft.com> wrote: >I have been...

Help I Am not Able To Delete My recieved E-mails.
I had recieved e-mails, after reading them, it said unable to delete. ...

Event Type: Information Event Source: Outlook Event Category: None Event ID: 27 Date: 9/13/2008 Time: 10:15:50 PM User: N/A Computer: IBM_M52 Description: The operation failed. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. I cannot access any "meat" of Outlook: no E-mail Accounts, Datafiles, Import/Export; nor can I send or receive. Have done Detect and Repair and complete reinstall to no avail. Did you make a new profile? http://www.slipstick.com/Tutorials/profile/profile.htm -- Diane Poremsky [MVP - Outlook] Outlook Tip...

Need help (Average excluding zero values)
This is what i have & it works but one problem there will be zero values & i need to exclude them if possible =AVERAGE(IF(AA5="L",E\$5:E\$65)) I have tried several different combinations I can,t get it that includes zero values in the average for me. are they blanks and not zeroes? -- Gary Keramidas Excel 2003 "Mike" <Mike@discussions.microsoft.com> wrote in message news:29096CBB-3E2A-4828-8A49-6CEB0142D5C4@microsoft.com... > This is what i have & it works but one problem there will be zero values & i > need to exclude them...

Some help with conditional formatting
Hi, I have a worksheet which details stock and last weeks sales. e,g, column A is stock, column B is 7-day sales. This extends down the worksheet several hundred lines (1 line for each product I sell) I want to put in a conditional format which will turn Column A red if it is less than column B. I have no difficulty in doing this with one cell, however I can't seem to paste this into another cell or range of cells using copy/paste special or the format painter. When I do paste into another cell the condition is copied exactly. What I want to do is write a condition where the cell...

CVS file won't open in Excel 97, Help Please!
I'm new at this so please bear with me. I am trying to open a CVS attachment. The attachment contains a spreadsheet with imported tab delimited files.The attachment automatically opens my Excel program. (I have tried to save the file and open but that doesn't work either). When the program tries to open I get the error message: SYLK: File format is not valid My excel program is Excel 97 Any help would be greatly appreciated. This was posted by Myrna Larson: =========== >From - Wed Apr 12 17:18:13 2000 From: Myrna Larson <myrnalarson@home.net> Subject: Re: file form...

Help needed in excel transpose #2
I need a macro to transpose data in the excel.The data that i have is in the following format: Summary Some data Steps 1)Some data 2)Some data Expected Result Some data Keywords Some data This is repeated in the next row and so on.. I need it in the following format Summary Steps Expected Result Keywords somedata 1)Some data Some data Some data 2)Some data Summary Steps Expected Result Keywords somedata 1)Some data Some data Some data 2)Some data Note that th...

How do Count a the number of times a date range appears within a date range!?
I'm stumped, I have no real idea on how to do this so any help much appreciated! I have a start date (no year ie, 1st December) and an end date (again no year say 5th Jan), And a date range, eg 11th May 2001 to 1 January 2006 for info late: 1st Dec to 5th Jan = 35 days How do i get a count of the number of 1st Decemebers to 5th Januarys in that date range for example 1st December 2001 to 5th January 2002 is 1st 1st December 2002 to 5th January 2003 is 2nd 1st December 2003 to 5th January 2004 is 3rd 1st December 2004 to 5th January 2005 is 4th 1st December 2005 to 1th January 2006 is...

How to change transaction date?
I'm using Money 2003 Deluxe and Business. I use online bill payment through my bank. There is a recurring rental payment each month. It is processed on the 20th for a due payment on the 25th. Money will see it and enter it into my Money account as an electronically cleared item. Sometimes the item will clear so that I have two rent payments occuring in the same month. This skews my expense report because the next month has no rental payment. I'd like to see one rent payment each month for budgeting purposes. Right now, I'm subtracting one of the rental payments manually to g...

GetMessage Function MFC Mixed mode dll link problems...
All, I have a MFC Mixed mode dll which is working well. I am now tring to use a regular C++ class from another DLL which has a method called GetMessage. When I link I get 2 error messages: MyClass.obj : error LNK2028: unresolved token (0A00074C) "public: class CMessage * __thiscall CTransmitMessage::GetMessage(int)" (?GetMessage@CTransmitMessage@DSS@@\$\$FQAEPAVCMessage@2@H@Z) referenced in function "private: void __thiscall CMyClass::ProcessStateResponse(class CStateResponse const &)" (?ProcessStateResponse@CMyClass@DSS@@\$\$FAAEXABVCStateResponse@2@@Z) MyClass.o...

MS templates open without help pane
Hiya, Hope you can help. I have used a number of Microsoft Excel Templates. My question is how can I open these spreadsheets without the online help pane opening automatically? Thanks in advance -- Carys Hopefully I understand this right. You mean the window on the side (getting started or whatever)? If so you should be able to go to tools -> Options and in the view tab uncheck Startup Task Pane. If thats not what you mean let me know...I'm sort of new at this heh "Carys" wrote: > Hiya, > > Hope you can help. I have used a number of Microsoft Excel Templat...