day of the week and date formula

Hello

I am looking to write a formula that gives me the difference in hours 
between a logged date and time and a closed date and time  eg -27/11/2009 
09:23:26 and
 30/11/2009 10:34:20.  Once I have this I need to calculate the working 
hours used to resolve the issue.  If the duration of the time includes a 
weekend, a saturday would equate to 4 hours  working and a sunday would be 0 
hours working time, a week day equates to 11.5 hours working.

Any suggestions??

Thanks

-- 
Sarah
0
Utf
12/3/2009 8:03:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
782 Views

Similar Articles

[PageSpeed] 47

Hi,

we need to know what hours during any day are considered working - ie, do 
all hours of the day count as working hours or only certain ones?

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sarah Elliott" wrote:

> Hello
> 
> I am looking to write a formula that gives me the difference in hours 
> between a logged date and time and a closed date and time  eg -27/11/2009 
> 09:23:26 and
>  30/11/2009 10:34:20.  Once I have this I need to calculate the working 
> hours used to resolve the issue.  If the duration of the time includes a 
> weekend, a saturday would equate to 4 hours  working and a sunday would be 0 
> hours working time, a week day equates to 11.5 hours working.
> 
> Any suggestions??
> 
> Thanks
> 
> -- 
> Sarah
0
Utf
12/3/2009 9:02:11 PM
Hello Sarah,

I suggest to take mu UDF count_hours:
http://sulprobil.com/html/count_hours.html

Regards,
Bernd
0
Bernd
12/3/2009 10:55:15 PM
Reply:

Similar Artilces:

sales line detail window-after updating the request Ship Date-also
I am also using manufacturing, when you change the requested ship date, you get a window asking: You can update the In House due date, What is the name of this window in modifer? I would like to modify this form, so the No button is disables, I want to make sure they always click 'yes' How do I find this form in modifer? the form name is just 'Microsoft GP Dynamics' thanks -- Doug ...

Excel Formulae #4
I've been trying hard to find a formula which would enable me (from one cell) to Add several cells and Subtract the answer from another cell (subtracting items of expenditure from a starting total). I could manage this with Lotus - but Excel does not seem to behave the same. Advice would be much appreciated. Thanks, Ken. One way: Say you wanted to subtract A1, A2, A3, J4 and N5 from L6: =L6-SUM(A1:A3,J4,N5) In article <173d01c3fbd6$3df0f300$a401280a@phx.gbl>, "KenS." <anonymous@discussions.microsoft.com> wrote: > I've been trying hard ...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

Can excell notify me of due dates?
If I have due dates on my worksheet can I set something up so that I am notified on these dates? I've used this to help me. http://www.contextures.com/xlCondFormat01.html "Deion007" wrote: > If I have due dates on my worksheet can I set something up so that I am > notified on these dates? ...

Shorter Formula
Can anyone shorten this formula please. Basically all it does is gives me an average of the figures in Column "W" depending on the number of times that product appears in "R" column =IF(ISERROR(SUM(SUMIF($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF ($R$5:$R$43,R62)),0,SUM(SUMIF ($R$5:$R$9,R62,$W$5:$W$9),SUMIF ($R$22:$R$26,R62,$W$22:$W$26),SUMIF ($R$39:$R$43,R62,$W$39:$W$43))/COUNTIF($R$5:$R$43,R62)) thanks Pete I didn't try too hard to analyze your formula, just noted that your ranges and sum_ ranges...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Excel date changes
I have created a spreadsheet using excel which requires the use dates on a monthly basis (similar to a calendar). The problem I have is that every month I have to go in and change the dates on the spreadsheet to reflect those of the current month before printing. Is there a way I can make it so that excel does that for me every month. Thanks, I'd really appreciate the input. Please elaborate on what the spreadsheet looks like. "phonescoop@hotmail.com" wrote: > I have created a spreadsheet using excel which requires the use dates > on a monthly basis (similar to a cal...

Sum Days into Weeks
I would like to save myself a whole lot of work please... I have two spreadsheets, one has all the days of the year across the columns, and the next has all the week ending dates across the colums. What I need to do is sum the data in each of the rows below the daily dates into weekly chunks on the same rows in the Weekly spreadsheet. I have also added these up into montly chunks, but there was only 12 sums to do so wasn't too bad. I don't relish having to do 52 of them. Any assistance would be appreciated. If you have *all* the dates for a year across a row then you mus...

Dates #12
I have a rather looooong list of dates (mm/dd/yy). In a column beside it I want just month and year so I can, for example, find all books sold in a month range. Can you help? NO need where rngA has your dates and rngB is a count of the sales =sumproduct((year(rngA)=2004)*(month(rngA)=8)*(rngB)) to sum rngC if it had the price total for each date =sumproduct((year(rngA)=2004)*(month(rngA)=8)*rngC) -- Don Guillett SalesAid Software donaldb@281.com "Lyndie" <anonymous@discussions.microsoft.com> wrote in message news:83fd01c48543$7d211680$a501280a@phx.gbl... > I have a ...

Formula counts incorrectly
Help please. Column K contains dates and blank cells. I would like to count how many of these dates fall within a given date range. For example, in the first week in May. Column K contains these dates. 5/2/2005, 5/3/2005, 5/5/2005, 5/5/2005. I would like to count this as 4 dates within the first week of May. I created this formula but the output is 3. =SUMPRODUCT(--(ECNT!F2:F515>=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515))) I assume that it is only counting 5/5/2005 one time? I changed the dates in the formula to check a second 2nd date range (5/9 to ...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

Help writing a formula
I have a huge database that I need to create numbering for each line like this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the number doesn't change each line - sometimes the number will be duplicated on multiple lines because those lines have the same title, I also need to create another cell extending that letter/number combo that will differentiate the 1st cell. So the first cell stays the same if the title is the same. The second cell adds another layer of numbering to differentiate the first cell. See example: (Row1, Col 1-4) AK-1, AK-1-1, Title1...

Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1. How is the best way of doing this. Is there a shortcut....keyboard stroke to do this?? Hit [F4] several times and watch the results. Micky "hrb_2001" wrote: > I want to turn this =A1 into =$A$1. > How is the best way of doing this. Is there a shortcut....keyboard stroke to > do this?? > Hi, Enter =A1 then tap F4 Repeated taps of F4 scroll through the referencing options -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently an...

CLick on Box to enter time of day
I have a worksheet where payment is entered in column C. I would like to use Column B as a time stamp. In other words as soon as data is entered in C5 then the exact time comes up in B5. When data is entered in C6 then the exact time cones up in C5. I do not want this time to change even when the page is saved and opened later on. Elfego You can enter a static time in a cell by hitting CTRL + SHIFT + ;(semi-colon) You could also use event code to enter a static date when you enter something in a cell. Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in ...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

Re: Could someone please help me with formulas
sorry typo http://www.eaglepi.com/formula/example.xls "No" <no@isp.com> wrote in message news:... > I created a workbook and tried to explain the best I could how everything > should be. you can download the workbook at, > http://www.eaglepi.com/formlua/example.xls > > I really appreciate everyones help on this..... > > > "CLR" <croberts@tampabay.rr.com> wrote in message > news:%235IxtFEHFHA.2936@TK2MSFTNGP15.phx.gbl... > > Maybe in cell J2 you could put the formula =H2+I2, which would give you > the > > sum of the C...

Change the format of my footer {date} option?
I would like to show the date in my footer as January 25, 2005 instead of the default 01/25/05 Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub put this in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "Tori" <Tori@discussions.microsoft.com> wrote in message news:817363F1-32AB-4528-B1D6-304F8E712EA8@microsoft.com... > I would like to show the date in my footer as January 25, 2005 instead of the &g...

sorting activities for each lead/contact by actual end dates
When I view the activities associated with each lead and contacts, the actual end date are not shown. Does anyone know how I can dispaly this field? The activities list in workplace does show the actual end dates but we'd like to see that when we access the list through indivial contact/lead record. Thanks, Victoria Hi Victoria, There is no good out of the box way to do this in MSCRM today. We're working hard to fix this for v2.0. Many of our customers have worked around this issue by registering for a post call out on the activity creation, and then having a custom object wh...

turning off formula bar
I can't remember or find how to turn off the formula bar in Excel 2003? Can someone help please. This is the bar that displays cell contents up top. Thanks! --Randy Starkey Tools>Options>View tab, uncheck Formula bar -- Kind regards, Niek Otten Microsoft MVP - Excel "Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:12kcghhahv0oif4@corp.supernews.com... |I can't remember or find how to turn off the formula bar in Excel 2003? Can | someone help please. This is the bar that displays cell contents up top. | | Thanks! | | --...

Pasted fractions converted to dates
I'm trying to copy a table containing text, decimals and fractions from a word table into excel. How can i stop the fractions from becoming dates when pasted? ie. 1-5/8 becomes 1/5/2008 and 9/16 becomes 16-Sep. Its the hyphen that is causeing the trouble. A fraction in Excel has the form 1 5/8 with a space between the integer and the fractional parts Can you get rid of the hyphen in the Word doc? best wishes Bernard "Jon_C" <JonCCrouch@gmail.com> wrote in message news:1192110561.240426.147550@o3g2000hsb.googlegroups.com... > I'm trying to copy a table containin...

Auto copy dates from one cell to another
I have a multiple page worksheet. In A8 is for the Employee Name and in B8 is for the date. I would like for this information to automatically be placed at A96 and B96 which is the top of the second page within the worksheet and so on for the remainder of the pages. Position cursor in cell A96 and enter the formula: =A$8, and in B96 the formula =B$8 Copy these two cells to the appropriate cells on the other "pages" of your worksheet. Pete Hi Kelly, In cell A96 you fill out the formula =A8 In cell B96 you fill out the formula =B8 this formula tells excel to take the val...

Report to show totals for each day of month
Greetings, I have an Excel spreadsheet that I use to capture: Column A) Day of the month (1-May, 2-May, etc.) Column B) How many clients were in residence on each day (Occupancy) Column C) Total Capacity (27) Column D) Shows a "1" if the Occupancy is 90% or more of the Total Capacity, else "0" The Totals row shows: Column B) The average Occupancy for the month Column D) Totals the times there is a "1" in Column D Is there a way for me to capture this data in a Report? I've got a query set up that uses these expressions: For AdmitDate: <=[Dat...

counting dates <= 7 days ago based on criteria in a diff column
I have a spreadsheet that holds all tasks for a project. Column D holds a catagory and column Q holds the date closed. I need a formula (on a separate sheet) that counts all tasks of a specific category that were closed in the past 7 days. I already have a formula that calculates all tasks that were closed in the past 7 days, just need to add the additional criterion of the category. Hi, Try this =sumproduct((sheet1!D2:D30=A2)*((today()-sheet1!Q2:Q30)=7)) A2 on sheet2 has the specific category for which you want to count the closed tasks -- Regards, Ashish Mathur...

Formulas in a criteria area
Can a formula be used in a criteria area when extracting data from a table in Excel 2007? ...