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.
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.
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 ...
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.
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 ?
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?
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?
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.
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?
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.
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.
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.
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
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.
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
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?
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
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]
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.
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")))
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.
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.
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?
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.
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.
