How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.
I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.
I want to do it without having to enter the colon.
I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.
View 6 Replies View RelatedI have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
files: twilight sheet squirrel temperature
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
View 9 Replies View RelatedI have a time card that is split into sections so there are a lot of SUM formulas. However, I need to include a space for breaks - 10 min.
How do I make everything convert comfortably?
Ex. 5 hours allotted to person A. 5 hours to person B. 1 hour 40 allotted to tasks. And 20 min per day for breaks.
Is there a way to format the cell so that it displays hours and minutes
1:35 = 1hr35min ??
All the formats available show it as time. I need it as a quantity of time rather than time of day so that the formulas will add correctly.
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.
View 4 Replies View RelatedI am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
View 10 Replies View RelatedI'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
Excel 2003
H
I
O
P
Q
R
S
1
Start Work
Time Out
Day Count
[code].....
Suppose the data is:
beaut* 1
abc* 2
and I use the command:
Vlookup("beautiful", A1:B2, 2, false)
I want this formula to return 1. I have done it before, the other way around when the wildcard is used in the formula but not in data.
I have a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
View 4 Replies View RelatedI have a lot of information for various contacts, in the following format:
Name: John Smith
Address: 123 Spring Street
Phone: 555-5555
Email: example@example.com
Name: Jane Doe
Address: 456 Elm Street
Phone: 555-5555
Email: example2@example.org
Name: Richard Roe
Address: 10 Bridge Street
Phone: 444-4444
Email: example3@example.org
What I am trying to do is convert that information so that it looks in a spreadsheet as follows:
example.jpg
I feel fairly sure this is the kind of thing Excel is capable of doing, but despite much searching and experimenting can't figure out how to do.
I need to add small trendlines next to some data in a score card and also working out the calculating Compounded Annual Growth Rate.
1) how do i just extract the treadline, i have created the graph but don't know how to just extract the trendline
2) is there a simple calculation on excel to work out the CAGR?
Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:
Sheet A has 10,000 records with these fields: id#, name, address, place of employment.
Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.
Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.
Is it possible to allow access to multiple users on one spreadsheet but they can only access one tab at a time each?
prevent multiple changes for the same thing.
I am trying to create a spreadsheet that will show vacation time; accrued, taken, leftover. There are different accrual rates for years of service, and caps on carryover.
I have been trying to figure this out, however the accruals for employees over 1 year are higher than they should be.
Breakdowns are as follows:
1-2 years - 1 week vacation - 40 hours eligible for carryover each year
3-4 years - 2 weeks vacation - 80 hours eligible for carryover each year
5-9 years - 3 weeks vacation - 120 hours eligible for carryover each year
10+ year - 4 weeks vacation - 160 hours eligible for carryover each year
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times.
I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between $20 and $60....
how do i use worksheetfunction save to save a particular sheet everyday at a certain time e.g. 5.30pm to a particular folder.
Also the name of the file will change with the day.. e.g. 22_Apr_07.xls for today.
And to also refresh the page / recalc before saving at 5.30pm...
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
I need to calculate how much time I've got left before the earliest order needs to be despatched (it might be 2 or more on every day). This wouldn't be a problem if orders would be placed daily. But for every item it varies. For example: for "X" product there is an order in 3 days time to be despatched at 19:00. I have built a live clock in the spreadsheet but I can't work out the formula.
P.S. Also I need two time formats, first - days(text) hh:mm second - just a numeric value that can be formated as [hh:mm].
I ran my macro on a spreadsheet and it was fine.
But when I ran it from another spreadsheet I received the below error.
i.e. I placed the macro in book1.xls & from there open book2.xls.
ERROR:
HTML Run-time error '1004':
Application-defined or object-defined error
The error pops up when the macro reach the line "Rows ("3:3").select".
Having a problem with ComboBox on spreadsheet, not Userform. When the Sheet1 is first opened the ComboBox is not populated, if I click on Sheet2 and then Sheet1 ComboBox is populated and works fine. It appears that I need some additional coding.
View 3 Replies View RelatedNeed the code which would allow me to create a macro where when data is added into an excel sheet, it automatically adds in the date and the time?
View 2 Replies View RelatedWondering if there is an easy way to compare 2 spreadsheets that should have identical data on them? The first spreadsheet (Before) has the output data from 'before' a code fix was applied. The second spreadsheet (After) has the output data from 'after' a code fix was applied. The spreadsheets have 7 columns of data and almost 500 rows.
I've already copied the data from the source datasets provided by my IT folks into Notepad (.txt) files and then used Excel to open them as fixed width spreadsheets. I have 1 workbook with 1 spreadsheet with 'before' data. And, I have 1 workbook with 1 spreadsheet 'after' data. And, I have another workbook that contains both worksheets. So, I'm ready to go whenever I get hints of what to do next. :-)
I need to be able to show my client that we did not impact the data with the code fix that was applied. I want to be able to show my client contacts (business folks) an end result via Excel that confirms that I actually compared the 2 sheets and there were no differences. In other words....I can't just show them a formula with '0' as it end result (even tho that's basically what I'm trying to prove).
I need to write a macro that will import data contained in another spreadsheet, but am unsure how to do this. I have several (about 15) spreadsheets that contain data. I need to import key bits of this data into one central spreadsheet that will be used for reporting purposes. I only need 2 cells worth (values) from each source spreadsheet, to be pasted into the destination spreadsheet, into designated cells.
The source spreadsheets are usually closed down and kept on a file server, which my PC has access to. Ideally I want to activate this macro with a control button - i.e. I press the button once and the macro goes off and collects/updates each field with the latest data stored in each of the source spreadsheets.
I have a spreadsheet for payroll. The last column is for net pay. I enter this by hand when I get it back from the payroll tech. There are no formulas in this column. When done entering these figures, I click on "save". Then I close the file. From past experience with this sprdsht, I have found that it doesn't always save this column. It saves all the other info. I have entered in the sprdsht, but not this column. So, I have to re-enter everything in this column and "save" again. Close the sheet, then re-open it to be sure it got saved. Why won't it save this column of info. the first time?
View 4 Replies View RelatedI have a simple VBS script that puts the username & current time in columns. When the user saves that time is also placed into a column.
I would like to be able to calculate the amount of time a user has spent on the spreadsheet for the current month & if possible the total time all users have spent on the spreadsheet this months.
btw i saw following soloved post but i didnt get the question and answer.
can u provide examples?
http://www.excelforum.com/excel-misc...ard-count.html
I want to take random card dealings like in example:
So, 52 cards for 4 players. It doesn't matter where from formula takes values. Here is (yellow) just example in one column...
If any excel expert who know how to compare names that are similar to one other with a wild card,
for example
A
COLUMBUS STEEL CASTINGS CO. 2ND LIEN (AKA BP METALS)
B
BP METALS AKA COLUMBUS STEEL Total
A
LS POWER - BROADWAY GEN FUNDING LLC
B
BROADWAY GEN FUNDING, LLC Total
My live sheet is a lot more complex but this will help me well on my way.
Ill let the example say everything:
Sheet3
ABC1Condition 1Condition 2Condition 32red lorrybig £ 92,621.98 3yellow carbig£ 145,498.33 4yellow vanbig£ - 5pink carsmall£ - 6red carsmall£2,552,404.50 7pink lorrybig£ 862.37 8green carbig£ - 9green lorrybig£ 707,083.31 10red vansmall£ 30,010.73 11green carbig£ 2,276.13 12 13 14Total of all cars that are big: 15expected answer £ 147,774.46
Excel tables to the web >> Excel Jeanie HTML 4