I import a file and paste as CSV. I believe everything is considered text
then, but there are numbers as well. The worksheet has over 4000 lines. I
try to concantenate two cells (ie. a2 and b2). I use the function, but the
result shows the formula. What I want is the new value or the string of
words. I have checked my options and the auto calculate is turned on. I
even try the various F9 keys.
but, after subtracting those days when they fell between Holidays / vacation periods.
See question, in details, inside the attached workbook.
i had a sheet with the employee name,Working Place, Designation,Starting Time & Ending Time of each employee working in a Department. I want to track the no of hours each employee worked in the Format 1 Hr: 45 Mins .
i tried to get the result by substracting the ending time from starting time and then formatted the cells using the TIME FORMAT from the FORMAT Menu>Format Cells > TIME. But i m not getting the required result. I want a vba code or formula for extracting the no of Hrs & Mins the employee worked using the above formula.
I'm try to get the pos. placing in a auto sorted table to remain in place
i.e. 1st next to the top position. 2nd next tothe next position and so on down. but it keeps getting it wrong! I've tried amending the macro serval times but it makes things worse! Sorry can't attach the file, too big for this forum!!!!
I have a user form that has a label "txtcompno" which displays a "Auto incrementing" number everytime the user form is opened. e.g. 90001, 90002, 90003 and so on. This was working fine till yesterday, but has since stopped working for some reason: This is the code I wassuccessfully using before:
Private Sub UserForm_Activate()
Me.txtdate.Caption = Format(Now(), "dd/mm/yyyy")
Me.txtcompno.Enabled = True
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ComplaintData")
' find last data row from database
iRow = ws. Cells(Rows.Count, 1) _
.End(xlUp).Row
If ws.[A2].Value = "" Then
Me.txtcompno.Caption = 90001
Else
Me.txtcompno.Caption = ws.Cells(iRow, 1).Value + 1
End If
End Sub
but now its giving me error - "Run time error 13 : Type mis-match". and its highlighting the line: Me.txtcompno.Caption = ws.Cells(iRow, 1).Value + 1
1. Find the working days between 15th Nov 09 to 28th
2. Add a column that shows the date after a number of months from today date. The number of months from todays date for each of the country is mentioned in column No. of Months after which revised.
3. Find the date our training will end using excel considering the fact that SAT and SUN are holidays. (Use the date when your training started)
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
to calculate no.s of workdays between two given dates,
1.We work 06 days a week (so only Sunday is OFF)
2.What if I have some holiday between the given dates + Sunday OFF
I am currently using Excel 2007 and I am trying to find a good working formula for Normal Hours worked:
I have the following code for Total Hours, Time and a Half and Double Time hours but I can't seem to get the right formula for working up to 8 hours.
Whereas C11 = Total hours added for the day.
=MOD(SUMPRODUCT(C8:C10-C7:C9),1) =Total Hours Worked
=Normal work hours between 0 to 8 hour work day.
=MIN("04:00",MAX(0,C11-"08:00")) =Time and a Half hours over 8 hours worked.
=MAX(0,C11-"12:00") =Double Time hours over 12 hours worked
I have code for auto insert row, when i m getting to new row column Q formula not working
remove the auto generate serial number in column column A, only i want to insert auto with column Q formula to work.
find enclosed a worksheet for your ref.
Please see the below auto insert code;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim O As Range
Set O = Range("O:O")
If Intersect(Target, O) Is Nothing Then Exit Sub
Application.EnableEvents = False
roow = Target.Row
Cells(roow + 1, 1).Value = Cells(roow, 1).Value + 1
Cells(roow + 1, 2).Select
Rows(Target.Row + 2).Insert
Application.EnableEvents = True
End Sub
I have table and need to take out montly total for each worker...
Now...
Each hours in day have own factor. (I need total hours per day but for illustration)...
So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours
When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours
When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours
Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).
Here is some attachment:
Book1.xls
I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.
Below I calculated manually those numbers wich I want to be automated...
Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
I've got a spread sheet at work that is shared between 4 people. It's password protected and its been protected so only four of us can edit it. It was working fine until yesterday for no reason the auto filter stopped working. The drop down arrows are still there but nothing happens when you click on them?
The sheet was made on excel 2003 but one of the users uses the newer excel which we will all eventually get.
I'm fairly new to macros but somehow i managed to create all the required macros for my project through googling and the like. The problem is that i created my macro enabled excel file in XP and when i transferred it to Windows 7 the auto save to PDF macro stopped working with the Runtime Error '5' Invalid Procedure, call or Argument. The highlighted error is as follows.
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:="C:WindowsTemp emporaryexam.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
This auto save is supposed to work when a hyperlink is clicked or when the set time runs out (Working Macro).
I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm [code]....
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule.
We are open M-F and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically.
On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month.
Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08-20-2007). Is there a way to get Excel to do that?
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.
View 9 Replies View RelatedI need to calculate the total WORK-hours (09:00-17:30) between two
date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however
the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm
DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"
=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)
I have searched and found many examples of code that will lock cells once data has been entered. But for some reason, I cannot get it to work in my workbook. The weird thing is, I have successfully got them to work on blank workbooks. I unlock a range I want people to be able to edit, I protect the workbook, I enter the code, and it works. But I do the same thing on my workbook, nothing locks.
I've tried locking all the cells on a sheet, and only unlocking a small range, as in the examples (A1:A10), and I cant get it to work. I've tried not pre-protecting the sheet, I've tried 4 or 5 different examples of code. If it wasn't for the fact I've gotten it to work on blank workbooks, I'd think VBA was disabled or something. I can't post a copy of my workbook for you all to look at because its on a stand-alone computer at work (and the USB is disabled for info-sec).
Excel Version 2007
I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).
My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?
Every time i open my workbook it recalculates and takes ages
is there anything i can press to halt the recalculations so i can go in and change the formulas that are making it take so long.
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.
Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?
IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".
The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
What line of code would I need to used to turn off the auto-calc upon opening a workbook?
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
View 2 Replies View RelatedI have attached a chart I'm working on. Each item is assigned a part number and it automatically takes away from the inventory each time an item is sold. I was wondering if the bottom half could work with the top to determine profit.
Example:
Customer purchases Part # 3 (C15) Quantity 1 (D15) for $450 (E15). Is there any way to set it up so whatever part number they enter...it subtracts their sold price from the cost (C5 through C11) to show the profit in (F15).
I have a workbook where I would like to enter the date of the starting month when the workbook is being used, and I want it to calculate in the next cell across the ending of that month.
View 4 Replies View RelatedI have some VBA that disables AutoCalculate on certain sheets of my workbook. I am looking for a method to re-enable auto-calculate upon exiting / closing the workbook, so that my users dont get stuck with auto-calculate being off for other workbooks.
View 2 Replies View Relatedi can place a tick box on a worksheet that will turn auto calc on/off? The same exact as going through the tools menu but for a lazy person?
View 2 Replies View RelatedI have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?
I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08
I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09
I am trying to keep a running total of cost, here is what I am doing
A B C D E F G H I
Date Books Pencils tax Month Books Pencils Tax Total
1/2/2014 11.50 2.50 .76 January 35.85 13.50 3.44 52.79
1/5/2014 14.50 3.85 .83 February 10.95 1.50 .68 13.13
1/6/2014 9.85 7.15 1.85
2/6/2014 10.95 1.50 .68
I only used Jan and Feb as an example. So as you see on the right side I will have all 12 months for the year and I just want all the totals for each month to auto calculate. I will have between 15 and 30 entries each month.
auto calculate end dates on production plan ...
View 9 Replies View Related