Times Sheet Working Am To Am
Jul 29, 2006
I am trying to do a basic time sheet for a friend and i know NOTHING about VBa so dont use it..
I simply want to work out how many hours someone works and was going along nicely until someone work over 12 hours.
I have
Start time end time hours worked
I formatted the boxes for hh:mm and the formula was end -start (easy)
then someone started at 8am and worked right throught to 5:30am and i get the answer as -2:30 but it should be 21:30
View 9 Replies
ADVERTISEMENT
Jun 10, 2013
I have a list a time (HH:MM:SS) that i want to sum together to get the total number of minutes.
It works if I use simply add A2+A3+A4 etc, but not when I use sum(a2:a4).
There are over 2500 lines of data.
Sheet attached : times.xlsx
View 3 Replies
View Related
Mar 22, 2007
I have 2 dates+ times.
1 the contract start date and time.
2 the contract end date and time.
Can I calculate the number of working hours between these two using a formula?
So if I have a 10 hour working day (08:00-18:00), Mon-Fri and the two Dates/Times can I calculate the number of working hours?
Example
Start 06-Mar-07 10:00
End 14-Mar-07 14:00
This is 2 part days and over a weekend.
View 9 Replies
View Related
Mar 2, 2010
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie :
Difference between
02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes
=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))
The following displays it in the Hrs and Mins format
=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))
View 9 Replies
View Related
May 7, 2014
Normally if you want to create a copy of a sheet what we do is :
Right click on the tab sheet > click on Move or copy > select (move to end) > check box Create a copy > click ok.
Now its very tedious if i have to copy the sheet lets say 50 times using this method. Perhaps there is another shorter way that will instantly create a copy of the sheet in the same workbook?
View 1 Replies
View Related
Jul 9, 2013
I would like to copy the existing sheet "Template" 17 times and each of the copied sheet should be names according to the order in the array:
"XX","TT","YY","WE","TG","KJ","IO","RT","EF","VU","GF","DW","QA","EZ","QU","OF","BB"
how this is done in VBA?
View 3 Replies
View Related
Apr 5, 2007
I would like to calculate the average number of hours worked per day in a particular week from my time sheet (in "Google spread sheets"). Daily hours are calculated in hr:min:sec format. Days not worked display 0:00:00. The formula I use to calculate total hours worked that weeK is for row 9 as follows: =INT( SUM(G9,K9,O9,S9,W9) )*24+LEFT(TEXT( SUM(G9,K9,O9,S9,W9) ,"HH:mm"),2)&TEXT( SUM(G9,K9,O9,S9,W9) , ":mm:ss")
How do I calculate the average number of hrs and minutes worked per day (excluding days not worked, cells containing 0:00:00)
View 9 Replies
View Related
Nov 2, 2008
I have one Excell Book with two work sheets. The 1st sheet (Sheet Name : Data ) contains the data... and in the 2nd sheet (Sheet Name : Rekey) i have a specific form in Rekey sheet and some data in Data sheet. Now i have to copy Rekey sheet into multiple times .... with different sheet names... and the sheet name are in Data sheet from cell A2 to end.....
For e.g. in Data Sheet cell A2 contins work1 and cell A3 contains work2 and A4 contains work3..... and so on....
Rekey sheet contains some form....
I need to paste Rekey sheet multiple times with sheet name work1, work2, work3..... and so on....
View 9 Replies
View Related
Apr 30, 2009
I am trying to create a time sheet where there will be a start time - end time - number of hours (difference between the 2) - pay rate - total pay, but am getting strange results!
View 9 Replies
View Related
Oct 8, 2013
With reference to attached file. I want to copy column D&E for 'n' number of times of value based on B1. If value in cell B2 is 0 then hide cell D&E and if there is any other value, excel to copy E&F to next column (leaving one column blank after each paste).
A
B
C
D
E
2
Product A
Price
VTA-2001
999
VTAA-2001
1299
XMV-1001
1000
View 5 Replies
View Related
May 3, 2014
take a look at sample worksheet, and click on the button "months". It will generate moths, and It should also insert sheets name in B13 cell. That is also good, but It should enter all names in upper case to that cell, in each sheet - but It doesn't. WHY ??
View 6 Replies
View Related
Dec 21, 2012
I am trying to set up excel sheets for documents that we print often. I would like to print them with page numbers in right footer that increases sequentially.
setting up a macro. But very time I print it starts from same number instead of the number after where I left off. Example: if I print today with page numbers 1 through 12, next time I want it to print number 13 through whatever no of copies printed.
Here is the code I copied from one of the threads here:
[Code] .....
I need the page number in right footer and also the macro to save the last number printed.
View 3 Replies
View Related
Feb 20, 2014
Data extract from sheet to sheet through column header values, i have set of data on sheet1,2..loop end of sheet
i want pull column data from multiple sheet column values into masterdata sheet depends upon column header values assigned in masterdata sheet.. my code works fine! but i want pull data if column header repeats more than one also
SO_NBR this is one heading in multiple sheets i cant pull col values not more than once under if SO_NBR repeats more than once.
[Code] .....
Find the attachment mentioned as expected result sheets is my requirement : HEADER_UPDATES_AIO_$.xls
View 12 Replies
View Related
Jun 18, 2012
I need excel 2010 to automatically save at 09:30 and 19:45 every day monday-friday. Also need the file name saved to reflect the time and date it was saved - if possible.
My PC locks after 10 minutes of inactivity - unfortunately this has to happen because of where my PC is situated.
View 1 Replies
View Related
Aug 27, 2009
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
View 9 Replies
View Related
Jun 9, 2014
I'm currently using a vba script to unprotect all of my worksheets within my workbook in order to refresh the pivots and then protect them again once the pivots are completed, but there are two worksheets I would like to remain unprotected in order for users to be able to edit them after the pivots have been refreshed. Now I've tried adding a sub code in order to unprotect one of the sheets as a test to see if it will allow me to edit the worksheet after I update the pivots. Unfortunately the coding isn't working, and the worksheet is still remaining in protect mode. Here's the code I am using to protect the worksheets when refreshing the pivot as well as the code I am attempting to use in order to unprotect the one worksheet.
View 4 Replies
View Related
Nov 9, 2008
I have used the below code to make hide/unhide some columns in my excel sheet. It works fine. Now, I need to protrect the sheet except the coloured cells. So I unlocked the cell by clicking the property of the colored cells & then did this :- tools- protection-Protect sheet - protect unlocked cells. But after protecting this sheet the macro code of hiding/unhiding of the columns are NOT WORKING & Errors is coming. I have used the below code:-
View 4 Replies
View Related
Sep 19, 2013
I have certain cells which I want the formulas hidden to the end user and protected so they can't update them.
I've locked and hidden these cells and protected the sheet however my Macro's fail to work thereafter.
Protect the sheet for the user interface only. This has to be done in VBA:
- Activate the sheet.
- Activate the Visual Basic Editor (Alt+F11).
- Activate the Immediate window (Ctrl+G).
- Type
ActiveSheet.Unprotect
- Press Enter.
- Type
ActiveSheet.Protect UserInterfaceOnly:=True
- Press Enter.
The sheet is now protected for the end user, but VBA code can freely manipulate it.This works well however when I go to another page and do the same it stops working on the original sheet.I would like to know if there is an easy way to:
protect/unprotect all sheets in one go and have the macros still work?
When I protect pages individually I want the following to be allowed:
Select unlocked/locked cells
Format
Sort
Use autofilter
use pivottable
I will first save which cells are un/locked & hidden.
View 9 Replies
View Related
Jul 3, 2008
if anyone has some vba code that can help with keeping latest duplicates when pasting to another worksheet.
Basically, I have a worksheet with YTD data and another worksheet with data for the current month. I need to paste the data from the Month to the YTD sheet. However, if the data (a reference number in column A) already exists, the row from the Month data should be pasted over the duplicate in the YTD sheet, and if it doesn't exists, it should paste it as a new row.
At the moment I'm just copying the whole lot to the YTD sheet and then try to remove duplicates, however, once it's pasted, I don't know which ones to remove.....
View 9 Replies
View Related
Jun 1, 2009
I have a excel worksheet with hyperlink. If the sheet is protected I am unable to use the hyperlink even though I have unblocked the protected cells.
View 9 Replies
View Related
Feb 16, 2010
Got most of this code from the web and I can't get it to work. The part I added was the array and loop bit. As a test I specifically renamed one of the sheets in the file to be something NOT in the array, but it still goes through like it exists (I.e. shexist=True). What did I miss?
Dim wsname As String, shexist As Boolean
myarray = Array("Statement of Values", "Vehicle", "Driver Info.", "Revenues by Discipline", "Revenues Geographically", "Employee-Payroll Info. CDN & US", "U.S. Payroll", "Employee-Payroll Info. FOREIGN")
For i = 0 To WorksheetFunction.CountA(myarray) - 1
shexist = False
On Error Resume Next
wsname = myarray(i)
shexist = CBool(Len(ActiveWorkbook.Sheets.Item(wsname).Name))
On Error GoTo 0
If shexist = False Then
MsgBox "The worksheet '" & wsname & "' does not exist in this file or has been renamed." & _
vbCr & "Please check the file and try again.", vbExclamation, "Consolidate"
GoTo THEEND
End If
Next i
View 9 Replies
View Related
Mar 23, 2013
I am trying to build a sheet to track deliveries into the company I work for.
We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.
View 2 Replies
View Related
Nov 9, 2009
I have a workbook with multiple worksheets. Each worksheet corresponds to a certain store fixture estimate. Ont these sheets I have a specific cell where you can input how many fixtures of that type are to be used.
On that sheet also, is a range of cells (ex. Range("A65:F3340")) that needs to be copied to a new summations sheet of total hours to build the project.
If sheet 1 has 1 fixture - the macro should copy the range of cells only once.
Sheet 2 has 4 fixtures - tha macro should copy the same range four times appending each set of data tot eh end of the previous, And so on for each fixture sheet.
View 3 Replies
View Related
Dec 7, 2011
How would I go about counting the number of times TA shows within strings of text in a range within a sheet. Example: TA,MH in cell A2, CB,TA in cell C40, ES,TA in cell Q19. Result would be 3. Ideally, I'd like the formula to reference a cell that has TA as the look up data such as in A1 I'd have TA.
View 4 Replies
View Related
Jan 24, 2014
I have two sheets and need information from Sheet titled Add-Del_chng positions to be moved to the sheet titled Cost Distribution.The issues here are :
1. The values in Cells A through J should be moved only if col Z value is 'hard coded"
2. The values should be moved to the 13th row.
3. This should happen every time the value is input into col Z of sheet titled add_del_chng.
Currently the macro does not run unless I go into the code and press play. I need this to happen automatically.
View 13 Replies
View Related
Jan 22, 2008
I am working on Excel 2000. I am creating a protected worksheet which will have conditional formatting and validation on manual input cells (formatted to light yellow). I want to make sure that people do not accidently forget and paste data into those cells. I read up on solutions from this thread; [url] and most of it seems to be more complex than what I needed (and I couldn't get it to work)
I copied some code from this website post
[url] to my worksheet.
It works fine if the Validation criteria is set up simultaneously (i.e. it's all the same) but does not work if the columns have different validation criteria. How can I modify it to work under those conditions? I have included two workbooks, one in which the val critieria is identical (and it works) and one in which it varies between columns (and it doesn't work). I also noted that if I modify the one that doesn't work to make it homogenious, it still doesn't work unless I go back and highlight all and reformat that way.
View 14 Replies
View Related
May 28, 2009
I was working on some code that would create a new folder in My Documents and within that folder export the current worksheet and save it there with a particular name. After doing so I need it to return to the original worksheet and clear contents. I would also need to export to the new sheet only contents of the cells without any macros or buttons.
View 5 Replies
View Related
Jul 23, 2009
Me.Rows.Autofit with a Protected Sheet not working.
Basically I want to use vb code which is
View 2 Replies
View Related
Dec 20, 2012
When I either manually check the box for allowing sorting and autofiltering, or I do it through vba with:
Code:
With Sheets("Inventory")
.Protect Password:="###", AllowSorting:=True, AllowFiltering:=True, AllowUserInterface:=True
.EnableAutoFilter = True
End With
I still am unable to use the icons. The icons are clickable to filter or sort, but every time they say that the cells I am selecting are protected. I want them to be protected from editing the data, but want to allow them to be sorted or filtered.
View 1 Replies
View Related
Jan 9, 2007
I am trying to password protect some of the cells in a worksheet. However doing that starts giving errors with Macros. The Macros are no longer working and infact giving error. What can I do to stop that happening?
View 9 Replies
View Related
