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.
When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?
find a formula that will calculate the hours between the two below values but only take in to consideration the business hours (from 9 to 17) and exclude any weekends?
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 have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!
For example Start time: 21:00 Finish time: 06:30 Hours worked: 9.50
What I need is a formular/vba code that can divide hours into different time ranges. The picture below shows an explanation.
In example 3 there time range analyzed goes from saturday till monday. Here Sunday should get 24 hours and the time range 23-08 for weekdays is at this point 00-08.
I've tried everything I could imagine to get this one working,
I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.
I have this spreadsheet where it is basically call center data so in one of the column it shows time of inbound call as example (21:12:05) and other columns shows specific notes about that call example if product was sold on that call or not. So I am trying to find trend that during what period of time in a day maximum product was sold. Example I want to set some criteria that from 8 am to 10 am, 10 am to 1 pm and so on and then to find how many products are sold. So what I am looking is how can i extract hours as numbers only? from time stamp? I tried using =RIGHT(a1,2) but its not working and it messing with the number format.
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.
We have a bar code scanner that scans employee IDs as they come and go each day. Beside the ID is the date/time stamp in the next column. They do not always come in in the same order they leave, so I am looking for a solution that will collect the in and out time for each ID, then add those times beside each employee name, and add them for a hourly total.
I have a started time of say 8am from cell B3. I want to know what time it will be when I add a full number (hours) from another cell. For example A3+B4 where A3 is 8:00 A.M. and B4 is 4.04. I'm looking to get the a result of 12:04 P.M.
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 m Trying to add some features to it and run into an issue. Here are the fields we are working with!
A1= 13:30:00 17/12/2008 (in other words 1:30pm on date given) - Need to know proper cell number format!!
B1= 23:00:00 (this represents 23 hours) - format for cell is [h]:mm:ss
C1= Unknown function (This field is my problem!!) Answer here SHOULD be "14:30:00 16/12/2008"
I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
I am trying to create a spreadsheet to determine hrs per time interval
i.e 06:00 - 14:00 14:00 - 22:00 22:00 - 06:00
So a start time of 06:00 and finish time of 14:00 would show 8hrs in first interval and 0 in the other 2 and a start time of 10:00 and a finish time of 18:00 would show 4hrs in first interval and 6 in second and 0 in last
I've currently got start time in A1 , finish time in B1 and want hours for interval 1 in D1 , interval 2 in E1 and interval 3 in F1.
I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.
The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.
Example:
If the call came in between 22:00 and 23:59 color is light green. If the call came in between 23:59 and 08:00 the color is yellow.
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.
I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
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.
