Quarterly Audits
I need to build a report that shows the progress of an audit and management responses and need help. Audit: At the end of each audit we count the open items from both the current findings and the previous quarters findings that are not resolved. I am trying to create a report that shows management how many findings are resolved and how many go unresolved. I need it to be as simple as possible.
Lou
excel.charting
18370 articles. 
0 followers.
1 Replies
418 Views
Laura Lou, Try this : Cell A1 , "Audit Issues", cell B1, This Month", cell C1, "Last Month". Cell A2, Open, A3,Closed,A4, Unresolved. Cell B2, # of Open, B3, # of Closed, B4, =SUM(B2-B3) Highlight cells A2 across to C2 and down to C5. Select the "Chart Wizard". I selected a BAR chart. Select "Next". The Wizard will label the data, you just have tell it which is "This Month" or "Last Month" on the "Series" tab. Good Luck. "Laura Lou" wrote: > I need to build a report that shows the progress of an audit and management > responses and need help. > > Audit: > At the end of each audit we count the open items from both the current > findings and the previous quarters findings that are not resolved. > > I am trying to create a report that shows management how many findings are > resolved and how many go unresolved. I need it to be as simple as possible.
FloMM2
Similar Artilces:
Vendor Audit Management in GP
Does GP or any add on prevent a purchasing user from ordering a product from an unapproved vendor or supplier What you might try is limiting access to the Vendor/Item cards. If you want to purchase Item A from Vendor X there MUST be a Vendor Item card for that combination. If purchasing staff cannot create these records, they cannot put the items on the PO. We are covering exactly this issue in this week's newsletter from Accolade. If you are not subscribed (its free), go to our website, visit the Subscribe To Our Newsletter link, and view this weeks newsletter (releases Tuesday...
Audit delegation
Hi, Do you know a server-side method to know if a user have granted access to his mailbox (delegation) to other users? Thank you. Hey cool, I can re-use the thingy I just told someone in another thread. Just look at the publicDelegates property using adsiedit or ldp or something, or use this: >dsquery * domainroot -filter >"(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -limit >1000 -attr displayName publicDelegates (replace the -limit 1000 if you want to limit it to different than 1000 items audited; without that switch it defaults to 100) ...
Exchange User Auditing
I have been asked by upper management to audit a users e-mail activity in our organization. I am required to audit all e-mail activity going to, and coming from his account. We are currently running Exchange Server 2003 Enterprise Edition with SP1 installed. Is there an easy way to do this? Thanks in advance... On Tue, 30 May 2006 07:48:26 -0600, "CoC" <CoC@nospam.postalias> wrote: >I have been asked by upper management to audit a users e-mail activity in >our organization. I am required to audit all e-mail activity going to, and >coming from his account. We...
Quarterly tax payments.
Will money actually help me make quarterly tax payments to the Feds and state. I know it helps calculate them but will it actually help fill out the forms and MAKE the payments???? In microsoft.public.money, iebfr14 wrote: >Will money actually help me make quarterly tax payments to the Feds and >state. I know it helps calculate them but will it actually help fill out the >forms and MAKE the payments???? No. ok thanks "Cal Learner-- MVP" wrote: > In microsoft.public.money, iebfr14 wrote: > > >Will money actually help me make quarterly tax payments to ...
Pivot Table Grouping by Quarter
I want to group my data based on Quarter but do not want it to be based on the standard calendar quarters (ie. Jan1- Mar31, Apr1-Jun30,Jul1-Sep30,Oct1-Dec31) but based on the first month in my data. For example, the starting month in my data is Aug1 so I would like my quarters to be base on: Aug1-Oct31, Nov1-Jan31, Feb1-Apr30, May1-Jul31 I can't seem to get Excel to do this with the Pivot Table grouping options. Is it possible? If not, what is my best option. Create a query in Access? Thanks in advance. Attila Fust Hi for this I would create a helper column in your source data...
how to shrink a page to copy 4 quarters
i have flyers that i want to make a full page of 4 to a page If you've already created the flier, use the "4 pages per sheet" option in the Zoom area of the Print dialog. If you haven't started yet, you could set up a 1/4 page layout using one of the "label" templates designed for "postcards." -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "shasha" <shasha@discussions.microsoft.com> wrote in message news:3F58A9C3-3372-4DC6-B6FF-A3F908288FC8@microsoft.com... >i...
Combine quarterly+monthly data in chart
Hi, I want to draw a chart which contains two series. One is monthl data and the other is quarterly. Is there a way of combining them ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Depends on your definition of "combining them." Depends on what kind of chart you had in mind. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <andym.xe4hm@excelfor...
Best chart to show quarterly forecast vs budget
Hi guys, Could you please give me your suggestions on what would be the best chart to show quarterly forecast to budget for multiple departments? Thanks, Andrea I would use a multiple bar comparitor to do that. It is the 3D bar graph that has one bar in front of the other bar. "andrrea" wrote: > Hi guys, > > Could you please give me your suggestions on what would be the best chart to > show quarterly forecast to budget for multiple departments? > > Thanks, > Andrea Thanks! Any other advice? "andrrea" wrote: > Hi guys, > > Could yo...
sumif to aggregate month to quarter
I have some monthly data in one sheet which I wish to transfer and aggregate to another sheet at a quarterly level. I couldn't figure out if sumif function would work here, or is any other methods? E.g. sheet 1 contains (and continue for many years): Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 sheet 2 contains (and continue for many years): 1Q01 2Q01 3Q01 Thanks If data to be summed are in row2, sheet1, then =SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3)) provided that years really begin with 01. -- Regards! Stefi „renegade” e...
Audit Trail 3rd party software
I am looking for a Audit Trail application that I can install in GP 7.5. Can anyone help please. -- Carlos Yes, Merit Solutions has one. Its http://www.meritsolutions.com/products/audit_trails.html "Carlos" wrote: > I am looking for a Audit Trail application that I can install in GP 7.5. Can > anyone help please. > > -- > Carlos Look at Auditor from www.Rocktonsoftware.com HS "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:8DB64C62-2CEE-473C-B99C-2ED53EBC66F1@microsoft.com... >I am looking for a Audit Trail application...
Formatting columns to show months, quarters, OR years
I deal with a lot of historical data. I usually use groups to be able to view this data by months, quarters, and years. So if I only want to see years I just click the highest level group. BUT...when I view the middle group (quarters) it also shows me the highest group. I have tried different ways of grouping but this is the best I can get. Is there another cool way to view data like this? Maybe by naming columns then have some sort of drop down box to choose what columns to view??? I am only an intermediate level, so please go easy... Thanks!!! Hi maybe a pivot table is what yo...
Calculating Interest where rate changes per quarter
A tax liability has been found, for simplicity say it is three years old. The tax authority announces its Interest rates per quarter, and often change. To calculate the accrued interest due after three plus years, has does one go about that? The samples all seem to suggest a constant interest rate. If only it were that simple. Hi, I think you will have to calculate it based on each of the quarterly changes over the past three years. The Tax Office does not do "averages". you will have to setup a table based on quarterly rates: September 2001 22%, December 2001 20%, and s...
Using Audit Tools with Worksheet Protection
One of the functions that is not available when a worksheet is protected is the Audit Tools - Precedents and Dependents These functions are quite useful. Does anyone know of any way or tools to still allow ausit tools when the worksheet is protected.? Have tried various addins without success as the worksheet protection inhibits their use thanks If the worksheet is protected then, presumably, you are the end-user, in which case the author has probably protected it for a good reason. If you are the author, then why not unprotect is so you can use these tools? -- Andy Wiggins FCCA www...
Quarter Totals
I currently have a crosstab query that I used to count the number of each type of quantity of volume per quarter but its giving me dollar amount vs a for whole count. For example: Q1 Q2 Q3 Q4 text $50.00 $25.00 How will I be able to have this read as a whole number? Are you saying that "50" and "25" are the correct numbers, but that the query is displaying these numbers as if they were currency? If so, in the query's design mode, you could try formatting that field ...
quarterly totals
How would i write the formula for entering 4 separte quarterly totals into a bar graph. Thanks Devko, Assume the months are in A1:A6 and the data is in B1:B6. Jan 5 Feb 3 Mar 6 Apr 4 May 2 Jun 5 The formula for quarter 1 is =AVERAGE(B1:B3) The formula for quarter 2 is =AVERAGE(B4:B6) Quarters 3 and 4 follow the same logic. ---- Regards, John Mansfield http://www.pdbook.com "Davko" wrote: > How would i write the formula for entering 4 separte quarterly totals into a > bar graph. Thanks > ...
audit a single mailbox logon
I know how to enable the logging at the server level which will be for all mailboxes on the server. But I don't need that except for just one maillbox. Is there a way to do that? Thanks. ...
Logon Auditing
I would like to be able to audit logons to non-primary mailboxes. I know I can do this through the Event 1016, however, these are both logons to others mailboxes and logons when viewing other's calendars from free/busy. Is there any way to differentiate these or is there another way to audit these? You can turn up diag logging on those events in the IS. ESM/Server/Properties/Diag Logging/IS/Logons. On Fri, 15 Apr 2005 12:28:02 -0700, "JJ" <JJ@discussions.microsoft.com> wrote: >I would like to be able to audit logons to non-primary mailboxes. I know I >can d...
Profiler Audit messages
I am running Profiler to track what is being done in my application and I am getting lots of: RPC:Completed exec sp_reset_connection Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_co...
How can I set month/quarter/annual date intervals
I have data collected on a daily basis (for work days) over several years, and I want to plot it on an X-Y plot versus date. I would like the monthly intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc. Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel just wants to put in the same number of days for the intervals. Any suggestions? It sounds like you are using an XY chart. For XY trend charts, I often run into the issue you are talking about frequently. I use a few formatting tricks: For my X Axis number format I double click the X axis to get Forma...
updated driver in OOBE(audit mode) no longer selected in normal mo
Greetings, We're trying to update our mouse filter driver to a newer revision inside OOBE(audit mode in W7). It seems the updated driver is properly loaded after rebooting to OOBE; however, if we switch back to normal mode, OS only selects the old driver. Is there any way to force the !audit mode OS to load the new driver updated in OOBE? Thanks. What's the difference between rebooting to OOBE and switching back to normal mode? It's not clear from that description what you mean. What process are you using to update the filter driver? Unless there is a sys...
Audit permissions and security in E5.5
Hi all Would like to overview permissions and security onm Distribution lists, mailboxes etc on a E5.5 Serve Are there any tools available to do thi TIA ...
Audit rights
We have some people coming in to run an audit on our systems. How can I provide them access to our systems but limit their access to view only? ...
Auditing Tools
I was wondering if anyone has run across good auditing tools to keep track of all changes to all entities? I have run across one commercial product (www.vscrm.com) and also the following article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crmauditing.asp) and was wondering if anyone found additional products? Thanks, Martin Martin- VAST is the only packaged application that I have heard of. Dave "Martin Kramer" <martink49@gmail.com> wrote in message news:Ov7PYf3wFHA.2620@TK2MSFTNGP09.phx.gbl... >I was wondering if anyo...
Producing quarterly figures?
Hi, I was here a few weeks back, and got some good advice for my financial analysis spreadsheet. I've been trying so solve a new puzzle this evening. I use a SUMPRODUCT expression to give me annual expenditure figures for the various categories I've used to breakdown my expenditure: =-SUMPRODUCT((0+('Money data'!$B$1:$B$3439<DATE(YEAR(C$4), MONTH(C $4)+1,1))),(0+(('Money data'!$B$1:$B$3439>=DATE(YEAR(C$4)-1,MONTH(C $4)+1,1)))),(0+(('Money data'!$H$1:$H$3439=$B8))),(0+(('Money data'!$G $1:$G$3439=$A8))),('Money data'!$F$1:$F$3439)) The w...
Quarter of the hour formula
I'm trying to find the formula to round the elapsed time of worked hours to the closest "QUARTER" of the hour. For example: Cell A1= 7:00 Cell B1= 15:43 The formula I need will result in 8.75 (where .75 represents 45 minutes, rounded off to the closest quarter of the hour). Simply subtracting the time doesn't cut it. Can anyone help? Thanks in advance Bluzdog Bluzdog, try this formula- =ROUND((A2-A1)*24*4,0)/4 If you want the time back in Excel Time format use this formula- =ROUND((A2-A1)*24*4,0)/4/24 and format as time. Please post again to say how you went. ...
Does GP or any add on prevent a purchasing user from ordering a product from an unapproved vendor or supplier What you might try is limiting access to the Vendor/Item cards. If you want to purchase Item A from Vendor X there MUST be a Vendor Item card for that combination. If purchasing staff cannot create these records, they cannot put the items on the PO. We are covering exactly this issue in this week's newsletter from Accolade. If you are not subscribed (its free), go to our website, visit the Subscribe To Our Newsletter link, and view this weeks newsletter (releases Tuesday...
Audit delegation
Hi, Do you know a server-side method to know if a user have granted access to his mailbox (delegation) to other users? Thank you. Hey cool, I can re-use the thingy I just told someone in another thread. Just look at the publicDelegates property using adsiedit or ldp or something, or use this: >dsquery * domainroot -filter >"(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -limit >1000 -attr displayName publicDelegates (replace the -limit 1000 if you want to limit it to different than 1000 items audited; without that switch it defaults to 100) ...
Exchange User Auditing
I have been asked by upper management to audit a users e-mail activity in our organization. I am required to audit all e-mail activity going to, and coming from his account. We are currently running Exchange Server 2003 Enterprise Edition with SP1 installed. Is there an easy way to do this? Thanks in advance... On Tue, 30 May 2006 07:48:26 -0600, "CoC" <CoC@nospam.postalias> wrote: >I have been asked by upper management to audit a users e-mail activity in >our organization. I am required to audit all e-mail activity going to, and >coming from his account. We...
Quarterly tax payments.
Will money actually help me make quarterly tax payments to the Feds and state. I know it helps calculate them but will it actually help fill out the forms and MAKE the payments???? In microsoft.public.money, iebfr14 wrote: >Will money actually help me make quarterly tax payments to the Feds and >state. I know it helps calculate them but will it actually help fill out the >forms and MAKE the payments???? No. ok thanks "Cal Learner-- MVP" wrote: > In microsoft.public.money, iebfr14 wrote: > > >Will money actually help me make quarterly tax payments to ...
Pivot Table Grouping by Quarter
I want to group my data based on Quarter but do not want it to be based on the standard calendar quarters (ie. Jan1- Mar31, Apr1-Jun30,Jul1-Sep30,Oct1-Dec31) but based on the first month in my data. For example, the starting month in my data is Aug1 so I would like my quarters to be base on: Aug1-Oct31, Nov1-Jan31, Feb1-Apr30, May1-Jul31 I can't seem to get Excel to do this with the Pivot Table grouping options. Is it possible? If not, what is my best option. Create a query in Access? Thanks in advance. Attila Fust Hi for this I would create a helper column in your source data...
how to shrink a page to copy 4 quarters
i have flyers that i want to make a full page of 4 to a page If you've already created the flier, use the "4 pages per sheet" option in the Zoom area of the Print dialog. If you haven't started yet, you could set up a 1/4 page layout using one of the "label" templates designed for "postcards." -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "shasha" <shasha@discussions.microsoft.com> wrote in message news:3F58A9C3-3372-4DC6-B6FF-A3F908288FC8@microsoft.com... >i...
Combine quarterly+monthly data in chart
Hi, I want to draw a chart which contains two series. One is monthl data and the other is quarterly. Is there a way of combining them ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Depends on your definition of "combining them." Depends on what kind of chart you had in mind. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <andym.xe4hm@excelfor...
Best chart to show quarterly forecast vs budget
Hi guys, Could you please give me your suggestions on what would be the best chart to show quarterly forecast to budget for multiple departments? Thanks, Andrea I would use a multiple bar comparitor to do that. It is the 3D bar graph that has one bar in front of the other bar. "andrrea" wrote: > Hi guys, > > Could you please give me your suggestions on what would be the best chart to > show quarterly forecast to budget for multiple departments? > > Thanks, > Andrea Thanks! Any other advice? "andrrea" wrote: > Hi guys, > > Could yo...
sumif to aggregate month to quarter
I have some monthly data in one sheet which I wish to transfer and aggregate to another sheet at a quarterly level. I couldn't figure out if sumif function would work here, or is any other methods? E.g. sheet 1 contains (and continue for many years): Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01 sheet 2 contains (and continue for many years): 1Q01 2Q01 3Q01 Thanks If data to be summed are in row2, sheet1, then =SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3)) provided that years really begin with 01. -- Regards! Stefi „renegade” e...
Audit Trail 3rd party software
I am looking for a Audit Trail application that I can install in GP 7.5. Can anyone help please. -- Carlos Yes, Merit Solutions has one. Its http://www.meritsolutions.com/products/audit_trails.html "Carlos" wrote: > I am looking for a Audit Trail application that I can install in GP 7.5. Can > anyone help please. > > -- > Carlos Look at Auditor from www.Rocktonsoftware.com HS "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:8DB64C62-2CEE-473C-B99C-2ED53EBC66F1@microsoft.com... >I am looking for a Audit Trail application...
Formatting columns to show months, quarters, OR years
I deal with a lot of historical data. I usually use groups to be able to view this data by months, quarters, and years. So if I only want to see years I just click the highest level group. BUT...when I view the middle group (quarters) it also shows me the highest group. I have tried different ways of grouping but this is the best I can get. Is there another cool way to view data like this? Maybe by naming columns then have some sort of drop down box to choose what columns to view??? I am only an intermediate level, so please go easy... Thanks!!! Hi maybe a pivot table is what yo...
Calculating Interest where rate changes per quarter
A tax liability has been found, for simplicity say it is three years old. The tax authority announces its Interest rates per quarter, and often change. To calculate the accrued interest due after three plus years, has does one go about that? The samples all seem to suggest a constant interest rate. If only it were that simple. Hi, I think you will have to calculate it based on each of the quarterly changes over the past three years. The Tax Office does not do "averages". you will have to setup a table based on quarterly rates: September 2001 22%, December 2001 20%, and s...
Using Audit Tools with Worksheet Protection
One of the functions that is not available when a worksheet is protected is the Audit Tools - Precedents and Dependents These functions are quite useful. Does anyone know of any way or tools to still allow ausit tools when the worksheet is protected.? Have tried various addins without success as the worksheet protection inhibits their use thanks If the worksheet is protected then, presumably, you are the end-user, in which case the author has probably protected it for a good reason. If you are the author, then why not unprotect is so you can use these tools? -- Andy Wiggins FCCA www...
Quarter Totals
I currently have a crosstab query that I used to count the number of each type of quantity of volume per quarter but its giving me dollar amount vs a for whole count. For example: Q1 Q2 Q3 Q4 text $50.00 $25.00 How will I be able to have this read as a whole number? Are you saying that "50" and "25" are the correct numbers, but that the query is displaying these numbers as if they were currency? If so, in the query's design mode, you could try formatting that field ...
quarterly totals
How would i write the formula for entering 4 separte quarterly totals into a bar graph. Thanks Devko, Assume the months are in A1:A6 and the data is in B1:B6. Jan 5 Feb 3 Mar 6 Apr 4 May 2 Jun 5 The formula for quarter 1 is =AVERAGE(B1:B3) The formula for quarter 2 is =AVERAGE(B4:B6) Quarters 3 and 4 follow the same logic. ---- Regards, John Mansfield http://www.pdbook.com "Davko" wrote: > How would i write the formula for entering 4 separte quarterly totals into a > bar graph. Thanks > ...
audit a single mailbox logon
I know how to enable the logging at the server level which will be for all mailboxes on the server. But I don't need that except for just one maillbox. Is there a way to do that? Thanks. ...
Logon Auditing
I would like to be able to audit logons to non-primary mailboxes. I know I can do this through the Event 1016, however, these are both logons to others mailboxes and logons when viewing other's calendars from free/busy. Is there any way to differentiate these or is there another way to audit these? You can turn up diag logging on those events in the IS. ESM/Server/Properties/Diag Logging/IS/Logons. On Fri, 15 Apr 2005 12:28:02 -0700, "JJ" <JJ@discussions.microsoft.com> wrote: >I would like to be able to audit logons to non-primary mailboxes. I know I >can d...
Profiler Audit messages
I am running Profiler to track what is being done in my application and I am getting lots of: RPC:Completed exec sp_reset_connection Audit Login -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_co...
How can I set month/quarter/annual date intervals
I have data collected on a daily basis (for work days) over several years, and I want to plot it on an X-Y plot versus date. I would like the monthly intervals showing on the scale to be like 1/1/04, 2/1/04, 3/1/04, etc. Similarly, for annual intervals, 1/1/03, 1/1/04, 1/1/05, etc. Excel just wants to put in the same number of days for the intervals. Any suggestions? It sounds like you are using an XY chart. For XY trend charts, I often run into the issue you are talking about frequently. I use a few formatting tricks: For my X Axis number format I double click the X axis to get Forma...
updated driver in OOBE(audit mode) no longer selected in normal mo
Greetings, We're trying to update our mouse filter driver to a newer revision inside OOBE(audit mode in W7). It seems the updated driver is properly loaded after rebooting to OOBE; however, if we switch back to normal mode, OS only selects the old driver. Is there any way to force the !audit mode OS to load the new driver updated in OOBE? Thanks. What's the difference between rebooting to OOBE and switching back to normal mode? It's not clear from that description what you mean. What process are you using to update the filter driver? Unless there is a sys...
Audit permissions and security in E5.5
Hi all Would like to overview permissions and security onm Distribution lists, mailboxes etc on a E5.5 Serve Are there any tools available to do thi TIA ...
Audit rights
We have some people coming in to run an audit on our systems. How can I provide them access to our systems but limit their access to view only? ...
Auditing Tools
I was wondering if anyone has run across good auditing tools to keep track of all changes to all entities? I have run across one commercial product (www.vscrm.com) and also the following article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crmauditing.asp) and was wondering if anyone found additional products? Thanks, Martin Martin- VAST is the only packaged application that I have heard of. Dave "Martin Kramer" <martink49@gmail.com> wrote in message news:Ov7PYf3wFHA.2620@TK2MSFTNGP09.phx.gbl... >I was wondering if anyo...
Producing quarterly figures?
Hi, I was here a few weeks back, and got some good advice for my financial analysis spreadsheet. I've been trying so solve a new puzzle this evening. I use a SUMPRODUCT expression to give me annual expenditure figures for the various categories I've used to breakdown my expenditure: =-SUMPRODUCT((0+('Money data'!$B$1:$B$3439<DATE(YEAR(C$4), MONTH(C $4)+1,1))),(0+(('Money data'!$B$1:$B$3439>=DATE(YEAR(C$4)-1,MONTH(C $4)+1,1)))),(0+(('Money data'!$H$1:$H$3439=$B8))),(0+(('Money data'!$G $1:$G$3439=$A8))),('Money data'!$F$1:$F$3439)) The w...
Quarter of the hour formula
I'm trying to find the formula to round the elapsed time of worked hours to the closest "QUARTER" of the hour. For example: Cell A1= 7:00 Cell B1= 15:43 The formula I need will result in 8.75 (where .75 represents 45 minutes, rounded off to the closest quarter of the hour). Simply subtracting the time doesn't cut it. Can anyone help? Thanks in advance Bluzdog Bluzdog, try this formula- =ROUND((A2-A1)*24*4,0)/4 If you want the time back in Excel Time format use this formula- =ROUND((A2-A1)*24*4,0)/4/24 and format as time. Please post again to say how you went. ...