Making A Time Sheet
Dec 1, 2009
I made a time sheet. In this time sheet i made a macro where when I press ctrl+t it fills in the date in a column and the time in the column next to it. So to start my day I would press ctrl+t and to end it i would goa column over and repeat the macro. and then i made it so that in the fifth column it adds the 2 times together with this formula "=IF(E27>0,(IF(G27>E27, G27-E27, 1+G27-E27)),"")" formula works fine unless you make a "mistake" and change the ending time manually to a time that is early then the starting time but on the same day. So I wanted to see if there is anyway of the total time column can return an error message if the ending time is earlier then the starting time by looking at the date in both date columns. Im going to attach the workbook to this post. on the tabs on the bottom it is under time sheet. and i think the macros should transfer over too. ctrl+t is the time macro if you want to try it out.
I may have not been so clear on explaining it so ask any questions if that is true.
View 13 Replies
ADVERTISEMENT
Jul 11, 2014
A client buys 500 minutes of my time. In one week I spend 340 minutes on the account. I'd like a column to show Time purchased (say 500 Minutes) Time spent and a final section showing time left (but showing negative values in red)
I hope I've explained this ok but here is an illustration of what I'm trying to achieve in Excel 2011.
[URL] ...........
View 2 Replies
View Related
Nov 25, 2009
I'm creating a calculator to determine the distance & travel time on a xy grid for a game. I'll share what I have so far to display the big picture. Here’s what I’ve accomplished so far:
#1) Cells A1 & B1 are dynamic, they are frequently changed so distance and time may be displayed. Cells A2 & B2 do not change.
#2) I use the following formula to determine the distance between cells, displayed in C2: =SQRT((A2-$A$1)^2+(B2-$B$1)^2)
#3) Next I multiply C2 by a number so that distance is measured correctly per unit type for this game. (I hide this column). Therefore, cell D3 has the following formula: =C2*20
#4) Now my problem, cell E3 - Correctly displaying time. I have the following formula in E3: =TIME(0,D2,0)
This works great!! That is until the time goes beyond 24 hours, then it rolls over. I've tried right clicking and setting the custom format of E3 to [hh]:mm, it still rolls over. I’ve also tried setting formatting to d:[hh]:mm, if I do this then the output time displayed is incorrect.
Ideally I would love a simple fix so that the time will roll over and show days. (I would like a display similar to d:hh:mm).
I’ve done a fair bit of research online and either I'm missing something simple or this is more complex than I thought. And not to make this any more confusing, but I would love whatever I accomplish here to be useable in compatibility mode ...
View 12 Replies
View Related
Dec 22, 2013
I'm creating a spreadsheet for anaesthesia monitoring. I have a table that calculates the time every 5 minutes (i.e. start time + 5 mins) for 3.5 hours and columns for heart rate, respiratory rate and drugs which is then plotted on a scatter graph. I've forced the major x-axis major unit to be 5 minutes.
Everything works fine for the first few values (1.JPG) then after 24 values it decides it wants to go crazy with the x-axis and display 24 hours (2.jpg) making everything unreadable. I would set the minimum manually but it obviously that will change depending on when the case starts. I can upload the file but it has some macros for other sections.
View 5 Replies
View Related
May 27, 2013
I have a list of jobs over a 24-hour period, that looks like this:
job1: 03:00am, duration 10 minutes
job2: 04:00am, duration 20 minutes
job3: 09:00am, duration 04 minutes
job4: 01:00pm, duration 65 minutes
Now I want to make a horizontal bar, that divides the bar into a 24-hour period (e.g. gray background) and fills the gaps that the system is in use with green parts, so in this example, the whole bar is gray and the part from 03:00am-03:10am+04:00am-04:20am+09:00am-09:04am + 01:00pm-02:05pm is filled in with green.
I have a list with about 300 of those jobs, so it would be nice if I could automate this. How to do this in Excel/VBA ?
View 1 Replies
View Related
Sep 18, 2013
I am trying to get excel to auto populate the current date and time each time I make an entry, however, each time i make an additional entry the current date and time populates but it changes all the prior entry's with that current date and time.I am trying to keep a log of all the times I make a new entry. Correct formula that will work?
View 9 Replies
View Related
Jun 8, 2007
I need to make a "aummary" or a pod sheet? what I need to do is make a page that will automatically tally up new information I put into new work sheets. right now I have 5 work sheets and the totals I need totaled are in different row #'s on each sheet. How can I do this so it will add the numbers automatically?
View 9 Replies
View Related
Jan 27, 2012
I need making a button that can move a row to another sheet.
I have a master sheet with the following data.
Col A - Name
Col B - address 1
Col C - City
Col D - State
Col E - Zip
Col F - Phone number
each row is a diffrent person.
the phone number will be the selected cell and I need a button to move the whole row to another sheet.
View 2 Replies
View Related
Nov 10, 2008
I have a workbook with about 53 worksheets one for every week in the year. They are named as follows:
VA-1-1-09 TO VA 12-28-09 All sheets are hidden except the (VA-1-1-09)
What I am trying to do is create some logic that would keep all 52 sheets Very hidden except sheet VA-1-1-09 Till the second week starts at that time what I would like to see happen sheet VA-1-5-09 come out of very hidden and appear visible and send sheet VA-1-1-09 to just a hidden state. I like to have this happen for every week of the year? If this can happen automatically that would be great however a command button clicked once a week would do the trick as well.
View 4 Replies
View Related
Jun 19, 2014
I have a userform that time stamps on my userform as soon as i open the form, is there a way that when I submit, that the amount of time that I was on the call to be put in my column on my worksheet as minutes?
View 2 Replies
View Related
Mar 13, 2008
I found a formula for calculating time in the HH:MM AM/PM
=IF(B1<A1,B1+1,B1)-A1
This formula was to give me total hours in the cell for which it is entered, and cell format for the formula was in military format.
I cannot find this post. The formula worked in OpenOffice Calc program, but when it came to converting to excel, I came up with #value!
I want to enter the time in 12 hour format and using am/pm to designate. I am making it for someone to make work schedules with and they do not know military time.
View 11 Replies
View Related
Aug 12, 2009
I am making a time sheet for work. It should be very simple but cant get it to work. I want time in, time out, lunch column, and Total. I don't want a lunch in and a lunch out though just a section where I can put my time in :30 minuets :45 or what ever. I also want it to work. on a 12 hour clock AM/PM .
Here are my column's
B2 Is Time In
C2 is Time out
D2 is Lunch time ( decimal ? )
E2 is the TOTAL with lunch deducted.
View 9 Replies
View Related
Apr 21, 2014
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
View 1 Replies
View Related
Apr 17, 2009
I am creating a employee time sheet.
I am doing calculation.
I used total hours formula is =(I5-B5+(I5<B5))*24
and also worked hours formula is =((I5-B5-(I5<B5))-(D5-C5+(D5<C5))-(F5-E5+(F5<E5))-(H5-G5+(H5<G5)))*24
Problem is don't show the correct time of total hrs & worked hrs
Here i am attaching my timesheet format.
View 14 Replies
View Related
Nov 21, 2009
I need to do is calculate the hours between two clock times minus 30 minutes.
Example:
Cell A1 - 4:00am
Cell B1 - 16:30
I need Cell C1 to say 12 (hours)
Also:
Cell A2 - 17:30
Cell B2 - 02:00am
I need cell c2 to say 8 (hours)
View 6 Replies
View Related
Feb 6, 2010
Attached is a layout that I am trying to get working.
the 1st tab is the input sheet, I want to be able to post input on that sheet and have it transfer to the other tabs in the appropriate fields.
I'm having a problem mostly with the vlookup to get the data into the right cells on the other tabs.
View 2 Replies
View Related
May 27, 2014
This is my line of code from Userform that fills Listbox from values of cell :
[Code] ....
Value in cell is time value formatted as h:mm, but when this value is being filled in Listbox, It changes to decimal value. Example : 12:00 is 0,5 on Listbox.
How can I change this to time value in Listbox ?
Tried with TimeValue() and Format(Time,"h:mm"), but one gives me error "type missmatch", and other just gives me back system time to each Listindex.
View 5 Replies
View Related
Jan 3, 2007
What iam chasing is a formula or something that when you enter hours for week (which is in AD column) *standard rate is 38 *
if you say put say 48 in that column it breaks the time down and puts 38 there and in column AI puts 3 hours and in column AJ Column puts 6 hours.but calculates each one with the dollar value.
******** ******************** ************************************************************************>Microsoft Excel - Wages.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)boutAC5AF5AC6AF6AC7AD7AF7AC8AD8AF8AC9AD9AF9AC10AD10AF10AC11AD11AC12AD12AC13AD13AC14AD14AD15AF15AG15AI15AJ15AL15AM15AN15=
ACADAEAFAGAHAIAJAKALAMAN4*HRSCPHCostHRST1/2CPT1/2CostHRSDTCPDTCost**50.038.019.23$730.740.0$28.84$0.000.0$38.46$0.00**60.038.015.31$581.783.0$22.96$0.002.0$30.62$0.00**76.529.413.03$383.300.0$19.54$0.000.0$26.06$0.00**80.022.815.33$348.760.0$22.99$0.000.0$30.66$0.00**96.526.815.64$419.670.0$23.46$0.000.0$31.28$0.00**100.011.511.5
View 9 Replies
View Related
Nov 23, 2007
I am putting together a time sheet for my peoples and can not figure out how to total the hours and remain in hour format, so I have the first employee's start time in B10, Stop time in B11 and total in B12, then the second in C10, C11 and C12 etc etc a grand total of all employees in H12 how would that be entered?
View 9 Replies
View Related
Feb 20, 2009
i have a time sheet that i just , was testing times and enter the time 02:00 pm to 10:00 pm when this is done 5 times for five days the HRS are ok = 40 but in my cell for over time witch should be 0:00 has ############################################ in it.
heres my codes, it only dos it on that time fram, if it go in to overtime its ok then. if i make it 6 days and i try 4 day at 2 to 10 and 1 day at any other time and its ok all works, why do 5 days of 2 to 10 do thsi and can it be fixed?
["Formula"]
'column "W"
=IF(T34-S34+V34-U34
View 9 Replies
View Related
Apr 8, 2009
I have problem to make an excel evidence which will track total working hours of employee during the month, how many times employee was late for work and how many times employee was on lunch break longer than 30 minutes.
And I made table which count all these things but problem is Saturday because on that day start work is 9::00 AM and all other days is 8:00
Formula for counting how many times employee was late is:
COUNTIF(D8:AC8,">0.336805555555555555555555555555")
where D8:AC8 is range where are all start work times in month and 0.336805555555555555555555555555 is 8:00 AM (serial number which is equivalen of 8:00 AM)
How to improve this formula to count properly because now it counts as a late when employee start work on Saturday on 9:00 AM (and that shouldnt count as a late)
[IMG]file:///C:/DOCUME%7E1/Nesa/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG]
View 9 Replies
View Related
Aug 4, 2006
Is there anyway to protect each sheet at one time? I have about 70 sheets and I dont wan to go to each and protect them. They all have the same password so I hope thats not a problem.
View 2 Replies
View Related
Nov 17, 2006
I want to rename a tab in Excell to the current date and time. Below is my code. Everything seems to work OK except that instead of insearting a time like this, "11_06 at 4_51 PM" it inserts "11_06 at 4_51 P11" I have no idea why its insert P11 in there.
ActiveSheet. Name = (Format(Now, "mm" & "_" & "yy" & " at " & Format(Now, "h_mm AM/PM")))
View 5 Replies
View Related
Mar 30, 2009
I have a worksheet that contains a 5 week shift pattern for workers. It is briefly laid out as:
JanFebAB
111330-21300700-1400
221230-20300700-1400
Months Jan to Dec 09 are there and shifts are divided up to ABCDE. I need another sheet to return the value of the shift time i.e. 1330-2130, probably via a lookup?? In my second sheet I have the Day number, the month and the shift letter.
View 2 Replies
View Related
Aug 8, 2013
can i freeze a row and a column in one time?
View 9 Replies
View Related
May 13, 2009
I want is when a user enters a time into a specific cell I want Excel to find that time in another sheet (which is in the same workbook) and give out the details of the cell which is next to it, see attached image file.
View 3 Replies
View Related
May 17, 2014
how to further formulate wages calculation.
I made a time sheet which calculates number of hours worked and so on. Now I would like it to calculate the wages as well. The problem is that there is a higher payment for nights.
- $16 per hour on regular hours
- $24 per hour from 23:00 to 07:00
How can I formulate it that I get the total payment taking into consideration nights?
View 4 Replies
View Related
Dec 21, 2006
a macro that automatically puts the cursor on a pre-specified cell everytime you click on that sheet?
View 9 Replies
View Related
Sep 22, 2004
I need to calculate the total hours worked for a series of 22 locations. Each Location has weekly allocated hours ranging form 0 to 80.
For example:
User1 worked 4 Hours on Monday at Location1 and 4 Hours at Location2.
User2 worked 4 Hours on Monday at Location1 and 4 Hours at Location3.
I am assuming I will need to use VBA for this calculation.
View 9 Replies
View Related
Feb 25, 2014
I have a time sheet that I can figure the daily OT and DT on, but how to calc the the Bi-Weekly totals.
I can only have 80 hours per week, the rest needs to be carried to the OT field, but I can only have 40 of OT per week and the rest needs to be carried to the DT.
View 1 Replies
View Related
