Question about Microsoft Office Professional 2007 Full Version for PC

Hi

I have a database of a person's working hours where I have his three months

extra or less working hours. I want an average of all the three months.

Can someone help me with the formula for this average. Here the average

formula is not giving the correct value.

Month 1 Month 2 Month 3 Average

-08:31:00 -24:50:00 21:01:00 ?

Hi Aviks,

Normal average works well for linear distribution, but here is non-linear distribution of work. So here is the formula that has been taken from project management concepts. Please try for different values of x and y

Try to keep x+y=6

=(StandardWorkingHr*x+(ActualWorkingHr-StandardWorkingHr)*y)/(x+y)

e.g.

=(StandardWorkingHr*5+(ActualWorkingHr-StandardWorkingHr)*1)/5)

if you can share data with me, I can give you the exact formula.

Posted on Mar 09, 2009

Format both cells with the time format "h:mm AM/PM".

Lets say the start time is in cell A2 and the stop time is in cell B2. In cell C2 put the formula =B2-A2 and custom format the cell C2 as: "h:mm"

Lets say the start time is in cell A2 and the stop time is in cell B2. In cell C2 put the formula =B2-A2 and custom format the cell C2 as: "h:mm"

Apr 16, 2009 | Microsoft Excel for PC

Its the same only the interface is different. Use the same technique of the Mathematical algorithm keeping in mind the way the date, time are displayed in their format. Also switch to 24 Hr mode....sodeep

Mar 20, 2009 | Microsoft Office Excel 2007

To calculate the difference in Years use =Year(c,r)-Year(c,r)

To calculate the difference in Months use =Month(c,r)-Month(c,r)

To calculate the difference in Months use =(c,r)-(c,r)

c= column, r= row

You will need to format the source cells as dates

To calculate the difference in Months use =Month(c,r)-Month(c,r)

To calculate the difference in Months use =(c,r)-(c,r)

c= column, r= row

You will need to format the source cells as dates

Mar 15, 2009 | Microsoft Office Excel 2003 for PC

use the below formula to get the date of the LWD (Monday thru Friday) of the month.

Assumed that if the date in cell A1 is 6-May-2005. then formula will be

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Assumed that if the date in cell A1 is 6-May-2005. then formula will be

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Jan 30, 2009 | Microsoft Excel for PC

Excel is a spreadsheet - it is less structured than a database and all of the data appears on one (or more) pages along with many calculations and summaries of data.

A database is structured so that all the information is kept in the same format for each member record of the database - Databases are better at processing larger volumes of information.

Some tasks can be performed equally well in either spreadsheet or database -

Often data is stored in a database but analysis is done in the spreadsheet.

A database is structured so that all the information is kept in the same format for each member record of the database - Databases are better at processing larger volumes of information.

Some tasks can be performed equally well in either spreadsheet or database -

Often data is stored in a database but analysis is done in the spreadsheet.

Nov 20, 2008 | Microsoft Excel for PC

Hello,

The formula should go like this:

=IF(C11>40,(C11-40)*D11,0)

This one will give you the result of (ONLY overtime hours)*(hourly wage), and if there are no overtime hours the result is 0.

Now, if you need to multiply that result with say 1,5 or whatever - insert the number you need like this:

=IF(C11>40,(C11-40)*D11*1.5,0)

If you need more help, please ask.

The formula should go like this:

=IF(C11>40,(C11-40)*D11,0)

This one will give you the result of (ONLY overtime hours)*(hourly wage), and if there are no overtime hours the result is 0.

Now, if you need to multiply that result with say 1,5 or whatever - insert the number you need like this:

=IF(C11>40,(C11-40)*D11*1.5,0)

If you need more help, please ask.

Sep 14, 2008 | Business & Productivity Software

I have created a spreadsheet for you to a) use and b) to learn from.

It is an Automated spreadsheet (as they should be) which calculates the number of minutes in a working week or month and calculates the average time per email giving Daily, Weekly and Monthly Outputs. It takes into account Public Holidays (or for time off). You can use the Output to create Graphs etc to visually display the Output.

It also allows you to calculate a Part Month average.

I have displayed it as it was CONSTRUCTED and as it would be USED.

The As Used worksheet is Protected and the only Inputs that can be done are in the Green Boxes (also the Saturday and Sunday boxes but you will need to Unhide the Validation List to include these and then to add 2 more columns titled Is Saturday? and Is Sunday? with the appropriate If Statement.

To unprotect the sheet go to Tools - Protection - Unprotect. There is no password so leave this blank.

All the workings are still there, the columns are just Hidden. To Unhide them, highlight the columns to the left and right of the hidden columns, click on Format - Columns - Unhide. To hide them again, highlight the columns that you want hidden, click on Format - Columns - Hide.

The LOGIC used (as in Functions) may seem complex but if you read the Descriptions in the first row you should be able to work out what and why it was done that way. Click on a cell to see what Function was used where.

You said that your spreadsheet was becoming a real mess, well I have created a monster for you (but not a mess).

I have uploaded the file to here:

http://users.tpg.com.au/lesliecl/

Hope this gives you the push to really start using Excel.

It is an Automated spreadsheet (as they should be) which calculates the number of minutes in a working week or month and calculates the average time per email giving Daily, Weekly and Monthly Outputs. It takes into account Public Holidays (or for time off). You can use the Output to create Graphs etc to visually display the Output.

It also allows you to calculate a Part Month average.

I have displayed it as it was CONSTRUCTED and as it would be USED.

The As Used worksheet is Protected and the only Inputs that can be done are in the Green Boxes (also the Saturday and Sunday boxes but you will need to Unhide the Validation List to include these and then to add 2 more columns titled Is Saturday? and Is Sunday? with the appropriate If Statement.

To unprotect the sheet go to Tools - Protection - Unprotect. There is no password so leave this blank.

All the workings are still there, the columns are just Hidden. To Unhide them, highlight the columns to the left and right of the hidden columns, click on Format - Columns - Unhide. To hide them again, highlight the columns that you want hidden, click on Format - Columns - Hide.

The LOGIC used (as in Functions) may seem complex but if you read the Descriptions in the first row you should be able to work out what and why it was done that way. Click on a cell to see what Function was used where.

You said that your spreadsheet was becoming a real mess, well I have created a monster for you (but not a mess).

I have uploaded the file to here:

http://users.tpg.com.au/lesliecl/

Hope this gives you the push to really start using Excel.

Apr 04, 2008 | Microsoft Excel for PC

Just enter using this style

**TIME IN**
**TIME OUT**
** HOURS**
10:00
16:50
6:50
10:22
17:34
7:12
9:00
10:00
1:00

the formula in HOURS is Time Out - Time In

the formula in HOURS is Time Out - Time In

Mar 20, 2008 | Business & Productivity Software

When i first figured out how to pull data from SQL and put the results in an excel file i referenced these two articles....

Reading and writing excel file using VB.NET (http://www.codeproject.com/KB/vb/Work_with_Excel__VBNET_.aspx)

Get the Values From DataBase and Stored into excell Sheet (http://www.codeproject.com/KB/vb/Getvaluesfromdatabase.aspx)

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"

Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"

Dim cnt As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim fld As ADODB.Field

'Open the connection.

cnt.Open(stcon)

'Open the recordset.

With rst

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _

ADODB.LockTypeEnum.adLockReadOnly , _

ADODB.CommandTypeEnum.adCmdText)

.ActiveConnection = Nothing 'Disconnect the Recordset.

End With

'Close the connection

cnt.Close ()

Dim exp As Export = New Export()

Dim xlApp As New Microsoft.Office.Interop.Excel.Application

Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )

Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")

Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation

Dim i As Short

'Turn off Excel's calculation.

With xlApp

xlCalc = .Calculation

.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual

End With

'Write the fieldnames.

For Each fld In rst.Fields

xlRange.Offset(0, i).Value = fld.Name

i = i + 1

Next

'Populate the range.

xlRange.Offset(1, 0).CopyFromRecordset(rst)

'Close the recordset.

rst.Close()

'Make Excel available to the user.

With xlApp

.Visible = True

.UserControl = True

'Restore the calculation mode.

.Calculation = xlCalc

End With

'Release variables from memory.

fld = Nothing

rst = Nothing

cnt = Nothing

xlRange = Nothing

xlWSheet = Nothing

xlWBook = Nothing

xlApp = Nothing

Reading and writing excel file using VB.NET (http://www.codeproject.com/KB/vb/Work_with_Excel__VBNET_.aspx)

Get the Values From DataBase and Stored into excell Sheet (http://www.codeproject.com/KB/vb/Getvaluesfromdatabase.aspx)

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"

Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"

Dim cnt As New ADODB.Connection

Dim rst As New ADODB.Recordset

Dim fld As ADODB.Field

'Open the connection.

cnt.Open(stcon)

'Open the recordset.

With rst

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _

ADODB.LockTypeEnum.adLockReadOnly , _

ADODB.CommandTypeEnum.adCmdText)

.ActiveConnection = Nothing 'Disconnect the Recordset.

End With

'Close the connection

cnt.Close ()

Dim exp As Export = New Export()

Dim xlApp As New Microsoft.Office.Interop.Excel.Application

Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )

Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")

Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation

Dim i As Short

'Turn off Excel's calculation.

With xlApp

xlCalc = .Calculation

.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual

End With

'Write the fieldnames.

For Each fld In rst.Fields

xlRange.Offset(0, i).Value = fld.Name

i = i + 1

Next

'Populate the range.

xlRange.Offset(1, 0).CopyFromRecordset(rst)

'Close the recordset.

rst.Close()

'Make Excel available to the user.

With xlApp

.Visible = True

.UserControl = True

'Restore the calculation mode.

.Calculation = xlCalc

End With

'Release variables from memory.

fld = Nothing

rst = Nothing

cnt = Nothing

xlRange = Nothing

xlWSheet = Nothing

xlWBook = Nothing

xlApp = Nothing

Jan 03, 2008 | Business & Productivity Software

If I understand correctly, you want to figure the normal wages at 40 hours and less. If there is more then 40 hours, calculate the normal 40 hours, then calculate the hours overtime (time and a half) and add them to get a total.

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

B5 columns are filled with this:

=IF(A5>$B$1,($B$2*$B$1)+((A5-$B$1)*($B$3)),A5*$B$2)

NOTE: The $ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a $ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Please rate as FixYa! or ask please clarify. ThankYa!

Oct 22, 2007 | Microsoft Excel 2003 (06503995)

220 people viewed this question

Usually answered in minutes!

×