Excel Times And Rounding?
Jan 29, 2014I'm trying to take a given time (c4) and round the time up or down to a 15 minute interval, the result should be + 45 or 60 minutes the given time.
View 5 Replies
ADVERTISEMENT
Excel Formula To Calculate Difference In Times But Only Between Specific Times
Apr 8, 2014I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
Rounding Between Excel And MRI (Host System)
Jun 15, 2009I have a workbook that the accountants use each month to supply our clients with thier monthly financials. One of the problems is rounding between Excel and MRI (Host System). I am using an array formula when totaling the various columns and this takes care of most rounding issues.
What I would like to do is have a macro that can be assigned to a command button that the account can click on when there is a rounding issue. The macro would ask the accountant which cell that needs to be adjusted (the cells are protected) and then another box would pop up asking for the amount (i.e. .01, -.01) then the macro would add this to the formula in that particular cell.
Excel 2007 :: Removing Decimals Without Rounding
Jan 29, 2014I have recived a CSV file with dollar amounts that need to be formated with out a deciaml and with our rounding. Example I need 12558.64 to be changed to 1255864.
View 3 Replies View RelatedExcel 2010 :: Rounding Number Of Years?
Sep 6, 2012I'm using the following:
B23=IF(A23="","",DATEDIF(A23,I3,"y"))
Where:
A23 = a date of installation
I3 = TODAY()
B23 = a number of years
It currently calculates correctly if the number of years correctly if it's older than 1 year. If under one year, it yeilds 0. I would like B23 to show 1 if the current formula yeilds 0.
I want it to yeild a 1 if the current calculation is 0.
Windows 7 Ultimate / Excel 2010
Banker's Rounding Or Scientific Rounding
Jul 9, 2008I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.
Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.
Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812
I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)
1) =MROUND(A2,0.001)
3) =ROUND(A2,3)
4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))
5) =EVEN(A2)
6) =ROUNDUP(A2,3)
7) =ROUNDDOWN(A2,3)
Excel 2010 :: Rounding Negative (and Positive) Time Values?
Apr 24, 2013EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.
View 9 Replies View RelatedExcel 2010 :: Lunch Break Times
Apr 11, 2014I am using excel 2010 and I am trying to find a formula whats going to give me added break with a set time but anything over a set time doesn't. I want it over 10:58:00 and under 11:32:00. I've got the numbers and tried with a formula but all whats happening is that all the data is adding 30mins break to the end time.
The sheet is using live times and data so the start times vary as do the due time.
Excel 2010 :: Sorting Multiple Times
Aug 27, 2013I have a sorting question in Excel 2010. Attached is an example workbook with a simplified version of the situation.
I want to sort a table multiple times. I have a table with part numbers and alphanumeric locations (Row, Shelf, Bay, Slot). I have formulas that divide up the location into 4 separate columns to be able to sort.
The first sort I do is by location, which I can easily achieve. The issue I have is sorting AGAIN by part number, while keeping the original sort somewhat intact. If a part number shows up multiple times (i.e. in two DIFFERENT locations), the Nth instance might show somewhere down the list.
Is there a way to sort my table to where you keep it in location order WHILE accounting for duplicates, which I would want grouped together in location order? Please see attached file : SortExample.xlsx
Excel 2010 :: What Causes A Macro To Run Slower At Certain Times
Apr 2, 2014When I run macros usually they're fast, but occasionally will run very slow. An example macro I have is pasted below. It's just a simple macro that pastes months. If I am pasting on top of a cell that contains a month it pastes the rest of the months that follow.
Most of the time this macro runs very fast, but occasionally it takes about 1-2 seconds to run, and it is a very simple macro! It is not macro specific, as all my macros will be slow. I''m using Windows 7 with Excel 2010. I'm only using 23% of my CPU and 4.5GB of my 16GB of RAM, so I don't think it's a hardware thing.
Is there some kind of condition that occurs that will make excel run macros slower?
[Code] .....
Get Excel To Ignore Seconds When Dealing With Times?
Nov 15, 2011How to get Excel to ignore "seconds" when dealing with times? That is, I NEED the seconds "portion" displayed, but need it to read "00" whenever any times are entered... So, when I type "02:34:44", I really need it to look like "02:34:00" (again, I need the "00" to still display).
View 3 Replies View RelatedExcel 2003 :: Average Readings Between Two Times?
Jan 5, 2012I have a sheet set up with date in Column A, time in column C and numbers in column D, starting in row 2 down. I made a pivot table with the dates grouped by day, 7, to show a week and I can group the time by hours, 6am, 7am, 8am. but what I want to do is group the time for times between 2 times, like from 6:00 AM to 10:00 AM, 10:01 AM to 2:00 PM and 2:01 PM to 8:00 PM, what I am wanting to do is group by week, group by the times above and average the numbers for that time. Is there anyway to do this in a pivot table? Or some other way? this is for use in Excel 2003
View 2 Replies View RelatedExcel 2007 :: Data Validation With 24:00 Times?
Jan 29, 2014I have a spreadsheet which monitors the hours employees work - one of the rules is that an employee must have 12 hours rest between shifts...
I am using the below Data Validation formula in column I to highlight when an employee has less than 12 hours rest and an alert to appear
=OR(AND(I4-B4=0.5),AND(I4-B4>=0,I4-B4>=0.5))
Now this works perfectly with the exception of when an employee finishes at say, 18:00hrs on a Friday, and starts at 22:00hrs on a Saturday - Excel Calculates the Rest hours as 04:00, when it should be 28:00
I've had a suggestion to use the following:
=I4+I2-B4-A2>=0.5
(I2 being the saturday date, A2 being the Friday date) - this does work but only for Cell I4, If I copy the Data Validation down the column all cell references change - is there a way to keep the I2 and A2 in the formula without having to retype in every row?
Excel 2003 :: Count Number Of Rows Between 2 Times
Feb 17, 2012I am using excel 2003. My sheet contains Times in column A and numbers in column B. I want to be able to count how many of those rows fall into time periods. Such as how many are from 8:00-9:00 AM. I do have the time column formatted as Time.
Ex:
A1: 3:40 PM
A2: 8:20 AM
A3: 8:55 AM
A4: 10:23 AM
etc.
B1: 23
B2: 34
B3: 12
B4: 35
etc.
So I want to be able to pull that between 8 and 9 AM there are 2 records.
The second part would be (in another cell) to pull the SUM of the numbers that correspond to the previous question. In the previous example it would be the SUM of all corresponding cells (Column B) that fall within the 8 and 9 AM range. Which would be 46 in this example.
Excel 2010 :: Formatting Military Times And Calculation
Jul 2, 2014I work for a freight company and have been trying to create a macro for a spreadsheet that I have to create every single day. I pull a report from a program called the AS400 and it sends me an excel spreadsheet with information about certain shipments such as close time and arrive time (which are in military time such as 16:00) along with driver names.
Anyways what I need to accomplish is to highlight the entire rows font in red if the driver arrived an hour or more past there close time. When I get the spreadsheet the military times are not showing in the HH:MM format under format cells. So I select the HH:MM format and try to do a formula such as Arrive time cell (M1) - close time cell (L1) to get the time difference it shows as #VALUE!. I've tried to convert it to different time formats and nothing works. The values just stay the same and will not allow me to do calculations with them.
At first I thought this was because the program was sending those values as text. I tried to use the date>text to columns>finish button on the columns but did not work. I've tried to remove the":" then use the HH:MM cell format but still wont work. I've even tried a time value formula to try to convert the values such as =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).
But nothing works it will not allow me to add or subtract from the time values no matter what I do. I am running excel 2010 on Windows XP the english version.
Counting In Excel - Determine Number Of Times Certain Item Is In Columns
Feb 7, 2014If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?
View 2 Replies View RelatedExcel 2007 :: Convert Dates And Times In A Column To Numbers
Aug 11, 2014I have a column in my data set that consists of dates and times in this format: 2014/08/02 01:46:49 PM. I am trying to convert these dates and times to numbers so that I can actually use these values for calculations and regression analysis. When I click on one of the cells, I get a number that is revealed along with decimals. For example, the cell with 2014/08/02 01:46:49 PM had a stored value of 41853.574. I tried to highlight the entire column of dates + times and click on the format cells button. I selected the number category under number but that did not work. I also tried to use the =DateValue(Cell #) function but that did not allow for distinguishing between the same date but different times.
What are my options for converting these dates and times to numbers that I can work with? Is there a way to get the entire column of dates + times to show each cell's numeric stored value?
Excel 2003 :: Perform Operation Variable Number Of Times?
Jan 29, 2014I would like to perform a sum of randomly generated numbers between 0 and 6, but I want the number of independent random numbers to be dependent on another cell. I have =SUM(RANDBETWEEN(0,6) and then I get stuck. Is there a way to perform the RANDBETWEEN(0,6) a number of times stated in another cell? In this case that value is 67 but it will vary from around 5 to 150. I think there is a way to do this with macros, but I am not versed in visual basic. I am using Excel 2003.
View 2 Replies View RelatedExcel 2007 :: Counting Number Of Times A Word Appears
Dec 12, 2011I have a 2007 Excel spreadsheet (saved as .XLS) with worker names in column A and error types in column B. Column B can have multiple entries (which are sometimes duplicative of each other) separated by a hard return.
What I need to do is run tallies to determine the number of errors by type for each person, counting the value every time it appears, even if it is more than once in a particular cell. The ultimate goal is to generate a formula to track the number of occurrences for all error types types for the person in Column A (i.e. one formula each to track ABC's Procedural errors, ABC's Technical errors, ABC's Admin errors, DEF's Procedural, etc) though ideally I just need a formula to calculate any one of those and I can edit it to get the rest. Here's a sample screenshot:
The COUNTIFS formula is where I started but that only seems to count cells with the value as opposed to occurrences of the value. I did find this formula in my searches but it doesn't seem to work:
=SUM(LEN(B1:B100)-LEN(SUBSTITUTE(B1:B100),"Procedural","")))/LEN("Procedural")
Excel 2010 :: Insert Row Data N Times Based On Cell Value
May 1, 2012I have a sheet that contains the following columns:
Invoice, Document #, Date, PO #, Part #, Part Description, Quantity, Net Amount
Based on the quantity in the row I need to copy the row, and insert it n-1 times. So if the quantity is 5, I need to copy and insert the data below the original row 4 times for a total of 5 rows of data.
I plan on firing the macro with a button as the data will change month to month.
Using Windows 7 and Excel 2010
Excel 2010 :: Save Sheet At Specific Times Daily?
Jun 18, 2012I need excel 2010 to automatically save at 09:30 and 19:45 every day monday-friday. Also need the file name saved to reflect the time and date it was saved - if possible.
My PC locks after 10 minutes of inactivity - unfortunately this has to happen because of where my PC is situated.
Excel 2007 :: VBA - Export Meeting Times Dates And Locations
Dec 7, 2012We are using Excel and Outlook 2007 and 2010 in our office and this program needs to work with both versions of each, Excel and Outlook. I need to have a csv file exported from outlook to identify locations of meetings and when they occur. I can do this manually but it could be used at multiple times by different people
View 4 Replies View RelatedGenerate Random Times In Excel Between Upper And Lower Limits?
Aug 23, 2013I want to generate random times in excel between upper and lower limits , which i can do , but i also want dates in same cells with out randomization , means dates should act independently and time should randomize
it should be something like that
8/1/2013 05:55 AM
8/2/2013 07:55 AM
8/3/2013 04:44 AM
Macro Loop Find Specific Times, Extract Date, Average Values Of Times
Feb 23, 2010In my workbook I have about 20 sheets (less in the attached sample), and on most sheets Id like to query data for each day and find an average value based on the time and then copy that result into another sheet. Im not sure if Ill need 2 macros for this or if one can be used.
Heres a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input 1:45 or 0:45 into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. Im flexible
1:45:
If I input 1:45, then Id like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
Excel 2010 :: Find Total Of Times Word Appears Across All Sheets In Column C?
Dec 19, 2013I have a single work book with 8 sheets (I am using Excel 2010 BTW) and I am trying to find a total of times a word appears across all the sheets in column "C"
I found this formula on another thread. =SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C1000"),"="&H3)) with an example. I made the changes that I needed for my purposes
This worked but only after I renamed the sheets to Sheet1, Sheet2, etc.
Is there a way get the same results from the above formula if all the sheets are named after our reps? Example: sheet1 is named Dan, Sheet2 is Nick, etc?
Excel 2010 :: Create Graph To Show Times Of Use For Particular Sporting Activities On Particular Days
Mar 20, 2014Excel 2010 - I need to create a graph that shows a week's use of a sportsground showing what sports were played, on which days and between which times. I thought I could show the times on the vertical axis, days on the horizontal axis and then the sports played in those times, however I'm unsure as to how to get the graph to recognise the times.
I've attached an example of some data and how I'd like to see it, I'm just not sure how I can do this using the chart set up!
Attached File : Graphs.xlsx
Excel 2010 :: Count Total Times Each Number Is In Large Group Of Numbers?
Feb 9, 2014How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
Convert Start Times And End Times And Minus
Mar 7, 2009I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
Excel 2003 :: Count How Many Times A Word Is In A Range / Word Can Be In Cell More Than Once
Feb 16, 2012I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")
But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
5
Test
8am-2pm
Test
5pm-10pm
Excel 2007 :: Get Two Cells To Be Used To Adjust Other Cells On Same Sheet Repeated Times
Jul 7, 2013I am trying to get two cells to be used to adjust other cells on the same sheet repeated times.
In a inventory sheet I have 5 columns as such , A1 previous balance, B1 Qty received, C1 qty on hand, D1 last cost, E1 current avg cost What I want, is to be able to enter my weekly received items in the B1 Qty received and the new cost in D1 Last cost cells and have them calculate my current average E1 and update my qty on hand C1 total. This in itself is not the biggest challenge. I was asking if there is a way that after the E1 current average cost and C1 qty on hand are updated by that formula, that the next time I enter a new B1 qty received and new cost in D1 Last cost cell they will update again basically without changing the earlier calculations achieved .
Example: Today I have item X with a A1 previous balance of 10 , with a D1 last cost of $1, and E1 current avg cost $1
I want to receive B1 10 more today at $.50 D1 last cost, which ideally would end up showing
A1 previous 10 (or 20 if adding) , C1 Qty on hand 20, D1 Last cost .5, E1 current avg $.75
Next week I want to receive B1 10 more at $.25 D1 last cost , which then would update showing
A1 previous balance 10 (or 30 if adding), Qty on hand 30, Last cost $.25 and E1 current avg $.58