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 ?
In Excel sheet1 Column G has Names(John,Augutus,Jennie ect..) In Coulmn H i need to daily update the Present or Absent status, once i update the status i need Coulmn I to automatically fetch the System name and Column J should update with Time and date..
I have a big system of data, me not knowing better i have it such that the categories is listed in the start of the row, and new data for periods are listed in the columns following. when i mark the data and click table, it puts the scroll down thing on top of the columns, which is of no use to me at all, i need them at the start of the row. is this possible, or do i need to reorganize all the data, and VBA code used to sort and collect it ?
I have a sheet with ascending week-ending dates in the row at the top.
Next row is the man-hours worked for each week.
Next row is the # of First Aids for each week.
Not every week has a first aid and I want to sum the number of hours worked since (not including) the week of the the last First Aid.
There is a summary sheet which pulls the data from any selected week via a HLOOKUP function. So, I'm thinking I'll add a hidden row with the new "Hours since last FA" under the # of FAs row and insert the magical formula into each cell in that row.
The dilemma is I can't come up with a formula/function to calculate the "hours since" data.
if there is a simple way to get the GMT equivalent in vba code. I am being a date/timestamp but realized that timezone differences may complicate matters, therefore I would like to "standardize" the timestamps into their GMT equivalent rather then using the computer's local time. I had been using
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.
Does anyone know how to make a form control (ex combobox, textbox) have multiple columns so that it would behave like 4 controls in one. What im going for is a control that looks like the control used when setting windows system time "12:30:00 AM" So "12" is in col 1, ":" is in col 2, "30" is in col 3, ":" is in col 4, "00" is in col 5, and "AM" is in col 6.
I have created a break tracker where i have to key in time manually. What i want is to click the desired label field and there will a button next to it which will deliver system time on that particular field while i will click on it. And i also want a working button for date as well. 2 separate button 1 for time and 1 for date.
I am currently using Excel 2007 and I am looking for an easier way to enter the system date and time into a cell. I am aware of the MS-Excel shortcuts but work in an environment where data capturers still often get it wrong.
Is it possible to use a Excel function/button to auto insert the system date and time by clicking on a cell/button or Alternatively use a calendar to do so.
I’m trying to program a specific label within a userform to continuously display the system time… I’ve tried to code something that loops while the userform.visibile property is true. This obviously just hangs the system… Is there an easier (or any other way) to do this?
I have a spreadsheet in which I want to track the date and time someone made an entry in an adjacent cell (so if someone enters something in B2, I want the date/time of that cell edit to appear in C2).
When the date/time has appeared, I want it to remain the same and not update when the worksheet is opened the next time. So I can't use NOW to pull it in, because that's going to change every time the file is opened.
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.
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.
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?
Columns B to M products with a product group (in row 1)
Prod Group..Core.......Core........Outer........Inner.........Core Supplier......Type A......Type B.......Type A........Type B.......Type C AB Ltd........1000.........2000..........500.............750...........5000 CD Ltd........3000.........5000..........100.............950...........8000 AB Ltd........2000.........4000..........600..............800..........7000
I would like to know how to sumif when for eg supplier is "AB Ltd" and the product type is "Core" in this eg = 21,000 (how to paste a table)
Hi, i need to change the second and third character from a 3 character word..
I need to change it into 2 characters with double Alphabetical order (TAA, TAB, TAC...TAZ, then TBA, TBB, TBC... TBZ, and so on).. kinda hard to explain.. it's like this:
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.
I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.
