I am trying to create a timeline spreadsheet for a weekly radio show I produce. We have 3 segments and the total time of the 3 must add to 59 minutes. Within each segments there are numerous variables with a certain time value. I am trying to figure out how to have time reduced correctly. For Example
Segment 1
Story A 5:00 minutes
Story B 4:30 minutes
Story C 3:00 minutes
What I need is Segment Total (A+B+C) in one cell and Remaining time (from total 59 minutes) in another. I know this may seem silly for most of you, but I cannot get the cells to format properly at all.
I am working on a project involving calculating time. It is a timesheet calculation. I was able to design the following layout:
.....A............B..........C..........D.......E.....F 1....Date.........Time IN....Time OUT...Hours... Total 2....01/01/07.....1830.......1930.......01:00...01:00 3....01/02/07.....1930.......2330.......04:00...05:00 4....01/03/07......830.......1900.......10:30...15:30 5 Column A is formatted for DATE. Columns B and C are GENERAL. Columns D and E are DATE format customized as '[hh]:mm'
The formula to calculate the time difference between the numbers in column B and C is located in column D. It is as follows: =IF(C4<1000,TIMEVALUE(LEFT(C4,1)&":"&RIGHT(C4,2)),TIMEVALUE(LEFT(C4,2)&":"&RIGHT(C4,2)))-IF(B4<1000,TIMEVALUE(LEFT(B4,1)&":"&RIGHT(B4,2)),TIMEVALUE(LEFT(B4,2)&":"&RIGHT(B4,2)))..................
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).
There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.
I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days.
I have attached a spreadsheet to help show the problem .......
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
I am trying to create a time stamp that shows elapsed time. So I enter my start time, and if I enter any text in cell B7, return the elapsed time in A7 based off of start time in A6. I have attached an example workbook.
I would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?
I have a list of numbers that I want to identify the highest and second highest numbers and create a mathematic result.
For example. I lets say I have 15 stocks "B19:P19" I have X amount of money to invest "Q21" I want to invest 85% of Q21 in the highest of B19:P19 and 15% in the second highest. If there are 2 highest that are tied I want to split the 85% of Q21. SO far I have been able to identify the Highest and show it at 85% of Q21 with this =IF(B19=(MAX(B19:P19)),(Q21*0.85)) but I am not sure how to get the rest of it to do what I want.
I have two columns... 1 with a list of dates (ex. 22-Apr-07) and the other which states either a pass or fail. Ignoring the day and year, I'm trying to get the total # of references for each month, and only count those with a 'pass'ing grade.
I have a date value in H2 - in I2 I want the date which is the date in G2 plus the number of days in H2, (so 20/01/2014 + 7 would give 27/01/2014 for example).
BUT - I also want it to account for a scenario where the value in G2 is 'No Expiry', so in that case I want I2 to show 'No End Date'.
I want me to track our inbox. All jobs that go into the inbox get logged on a sheet and at the end of the day get transferred to an Excel spreadsheet. They now want to track how long s job sits in the inbox before it's logged out and worked on. Currrently we put a time and date for both logged in and logged out. I've attached below a sample spreadsheet and a brief description as to some restrictions.
Our hours of work is 7AM to 6PM. So we only want to add add up the time the job was in the inbox based on those hours.... ie if a job came in right at 6PM at night and was logged out at 7:05AM in the morning, the total time that job was in the inbox was 5 mins. Same thing applies for the weekends.
I'm suppose to be in charge of creating that part of the spreadsheet.
************************************************************************>Microsoft Excel - work time.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB3= ABCDEFGH1********2*Job*#Time*inDate*inTime*outDate*outTime*spent*in*inbox*3*111:15*AM09/11/062:30*PM09/11/063*hours*&*15*minutes*4*24:25*PM09/11/069:45*AM09/12/064*hours*&*30*minutes*-*overnight*5*34:45*PM09/08/069:35*AM09/11/063*hours*&*50*minutes*-*over*weekend*6********7*hours*of*operation*are*7am*to*6pm***8********9*Explaination*or*#3*from*above….******10*Job*came*in*@*4:45*PM*on*Friday*but*was*not*removed*from*th*inbox*until*9:35*AM*on*Monday*morning.*11*Since*our*hours*of*operation*are*from*7AM*to*6PM,*the*time*that*the*job*was*sitting*in*the*inbox*was*3*hrs*&*50*mins.*12*This*is*figured*out*by*4:45PM*to*6PM*is*1*hr*&*15*mins*and*7AM*to*9:35AM*is*2*hrs*&*35*mins.**1hr,*15*mins*plus*2hrs,*35 *mins*equals*3hrs*&*50*mins*13********Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .
Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).
Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).
E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)" Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)" Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
Step 3 Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
I have just added two date and time picker controls to my spread sheet to allow the user to select the date range.
They are linked to cells C9 & C10.
The problem is that the formula I using to check whether the first selection pre-dates the second has stopped calculating correctly when using the date and time pricker controls.
The formula is:
=IF(C9>C10,"Error","OK")
I have checked it on the same sheet by just typing the dates in and it works fine...I have tried formatting cells C9 & C10 to short date, general, text, number but it still does not calculate correctly.
Not sure where the best to ask this is so i'll do it here.
I have a h:mm time which i need to get converted into days/hours/minutes, creating an on the fly phrase of something like "2 days, 4 hours, 32 mins" for example.
eg: 26:45 (hours/minuts) to be converted to "1 day(s), 2 hours, 45 minutes"...
it works well unless the staff member works past midnight. I get a negative hours worked value returned.
for eg
E4=8AM and D4 is 5PM i get an answer of 9 hours in F4, this is all good but if the start time E4=4PM and the finish is D4=1AM then I get the result of -15 hours in F4
I have a system that enters the ID in the first column, the date and time in the second and third columns and the sense (IN/OUT) in the fourth column, for each employee that enters/exits the premises. Note that not only the in /out can occur over midnight, but also I have the situation of having two periods of the same employee in the same day. The objective is to obtain in some way a daily report for each ID (employee).
I am trying to make a timesheet in Excel 2007 with a formula.
I want it to read: IN = 8:30 AM, OUT = 11:30 AM, IN = 12:30 PM, OUT = 4:30 PM
The total hours will be 8 because there is an hour for lunch.
And if I work a few hours one day and leave before lunch, I want the calculation to be right. I found a formula but it wouldn't add the lunch hour and I added a +1 in the formula but it makes the calculations wrong for when I work for only 2 or 3.
When people enter their hours I get them to do it in 24hr format, fine. BUT my problem is coming when I'm working out wages etc. I can get the user to enter 09:00 (start time) and 17:30 (end time) but then the cell works out the hours (cell 2-cell1) gives 8.30 in time format when I need it to show 8.5 (total hours worked) This means when it goes to work out wages, it takes 8.5*hourly rate not 8.3!!
I have made a work schedule for my local business and have set up a series of formulas that will fill out time cards that I could print out directly onto the paper time cards. The formulas that I have work except that if there are two subsequent entries that later will not return a value and result in an error.
If you could take a look at it that would be awesome. To use it you just need to type a name into the name column and a work time into the time column for that day. then in the other sheets( one for each worker ) it will set up the time card. The the error happens on Thursday, when Bob has an entry right after Fred. Then on Bobs sheet it gives me a #N/A.
i am creating a weekly time sheet for my company.the problem that i have is when the persons time reaches 40 hours, the time needs to be calculated in the overtime field. this is really tough for me when the person reaches 40 hours in the middle of the work day. I cant figure it out. i have attached the spreadsheet if you would like to look.
I have an Excel timesheet, and I am wondering if there is a way to have the timesheet default to PM after 12 noon, so the employees dont have to put in PM, they would just put their time and the sheet would default it to PM.
approach to sort out Day/Night Hours. Its bomb proof!
A new situation demands overtime payments......start and finish time can be any time day or night (crap job!), overtime is payable after 8 hours. Thus I have day (0600-1800) standard rate, day (0600-1800) overtime rate, night (1800-0600) standard rate, night (1800-0600) overtime rate.
So, starting at 1400 and finishing at 0100 give 4 hours day std + 4 hours std night + 3 hours night o/time; whereas starting at 0200 and finishing at 1300 gives 4 hours std night + 4 hours day std + 3 hours day o/time.
I'm using Excel 2003 and 2007 so use the Excel 97-2003 format.
Start Time, End Time, Break, Hours Worked. Then on the right hand side of my spreadsheet I started playing around with the current time etc.
I want to work out the time left in a working day(like a countdown), based on a variable number of hours of work in a day (here it is 7 hours) excl. breaksie. 7+breaktimeso I need 7+break - 'hours worked' to get hours and mins left I worked out how to get hours worked easily enough,
=J58-LOOKUP(TODAY(),A:A,D:D)-LOOKUP(TODAY(),A:A,B:B) where J58 is a cell that has the current time in it and D and B are the columns with the break time and start time in them.
I have a timesheet which calculates overtime and the sheet works ok. I've had help on this board constructing it and i'm well pleased so far. what i need for it now, is if there is an input cell with no data in it, i want the results cell to stay blank but at the moment i'm getting those horrid hash symbols.
The formula just now is end time minus start time, and from that a 45 minute break is deducted. The break is always the same so i have that in a cell on it's own and the formula does an end minus start, then - the break. What i'd like is when there's no data in the cells, leave the result cell blank...
I need to keep track of my salesmen's time. However, the company will not pay for the first 45 minutes so I need a formula that says, if the time entered in under "worked" is less than 45 minutes, no time will be deducted. If the time entered is equal to or greater than 45 minutes, 45 minutes will be deducted. These are the columns needed and the Daily Hours would be the total of all the previous columns. WORKED TRAVELED LUNCH DAILY HOURS
