Date And Time Differences With Blank Cells And Monthly Totals?
Jan 30, 2014
see the attached workbook with two different sheets (same data) using different formulas. Each has problems (red text) preventing me from moving forward.
I need to calculate time in level 1, time in level 2 and total time for each row and then be able to sum for the month. Unfortunately, sometimes there is missing data, but I still need to calculate everything possible using a consistent formula that can be applied to the column/row universally.
View 3 Replies
ADVERTISEMENT
Aug 11, 2006
I'm trying to devise a formula to produce "days in inventory" based on the following data:
Date In
Date Out (which may be blank if cargo still here)
Todays Date
Days in Whse (which is the formula I can't figure out!)
It needs to work like this, date out - date in, unless date out is null. If date out is null, the result should be calculated based off of todays date - date in.
View 3 Replies
View Related
Mar 21, 2006
Within 4 columns, I have a series of:
Start Dates / Start Times / End Dates / End Times
I need to create a formula which will give me the sum total of the difference between the data sets.
For example, the time difference between 8am on the 20th March and 1pm on the 21st March is 29 hours OR 1 day and 5 hours.
I need a formula which will calculate this for me.
View 11 Replies
View Related
Apr 24, 2014
I am wondering if there is way to analayse date and time data in a way that would allow me to look at time differences.
I have two columns of data, one has the date and time an entry was made onto a client database, the other shows the date and time an amendment or deletion was made.
What I would like is a formula that shows the time period difference between the two entries.
So for example if one column shows 14/02/2014 14:26 and the other shows 14/02/2014 14:28 it would show 1 (minute) as the result.
I have over 1000 entries to look at and manually it is taking a looooooooong time!
View 5 Replies
View Related
Oct 10, 2007
I have two identical web query's on the same sheet. One from column A to column I and the other one from column K to column S, and both have 404 rows. The one on the left (from column A to I) autorefreshes every 60 minutes and the one on the right (from column K to S) autorefreshes every 4 minutes. I want to subtract the numbers in column Q from column G and the ones in column R from column H, and in both cases if the result is bigger than 0 to place a timestamp in column Y(for Q-G), and in column Z (for R-H). I tried with NOW formula, but the timestamp changed every time the web query from the right autorefreshed, no matter of the result. I guess that I have to use VBA codes...but I'm not good at that .
View 9 Replies
View Related
May 20, 2009
I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.
I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.
I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.
Attached is my example.
View 6 Replies
View Related
Feb 24, 2006
best formula for adding figures in a column that correspond to each month of the year?
I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet.
View 10 Replies
View Related
Nov 26, 2011
Only recently have I really stumbled upon some of the more advanced functionality of excel, and I was literally blown away. I'm now trying to learn more and more about it.
[URL]
I need the Monthly totals of Apples, Oranges, Apples & Oranges.
I know how to select it by hand with the:
=SUM(, hold shift, mouse select the apples for March, and do the same for the other columns.
But how do you do it via a formula? Also, is it possible to make it dynamic? i.e. you add more transaction dates later, and the whole spread sheet gets updated automatically.
The only way I see how to do that is to write a loop that goes through all of the records. How would such a loop look like? Or is there a better way?
View 4 Replies
View Related
May 14, 2012
I've got data in E4:I4, Monday - Friday.
What I need to do, is add the total of those 5 figures in a total, but then clear the data in those 5 days.
Then when I input the data for the next week in to those days, I want it to add to the previos week.
So basically;
In week 1 the 'Total' is just week 1
In week 2, the 'Total' is week 1 & week 2'
I will only be going up to week 5. (only week 5 once in a 3 month period, so 2 4 weeks and a 5 week)
View 1 Replies
View Related
Apr 10, 2007
Spreadsheet is made up of Col A - Col AK:
Col A = Purchase Date
The info is continuous:
Row1 12/1/06
Row2 1/2/06
Row3 3/5/07
I need monthly totals to display simultaneously. Do not want to use Subtotals option.
View 9 Replies
View Related
Sep 9, 2013
I have a daily rainfall data and I want to convert it to monthly totals. I tried the pivotal table but the arrangement of the data seemed to be confusing (i.e the year, month and day are in different columns).
See the attached data: Raindata_excel_forum.xlsx‎
View 9 Replies
View Related
Feb 26, 2009
I assume there's a database (or pivot table?) solution for my task, which is to detect the days, weeks and months within a very long table, and obtain the totals of the data therein.
See attached worksheet.
View 8 Replies
View Related
Jun 3, 2006
I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.
View 14 Replies
View Related
Sep 22, 2011
How can I get a pivot table to calculate the average of the monthly totals and not the average of all the raw data.
Below is a pivot table of the data on the left. When the pivot table averages the months it calculates the average of every single record, 48.83. How can I get it to average the monthly totals:average(apr,may,Jun)= 179.05
Date cost Sum of cost Years Date
4/1/2011 -75.00 2011
2011 Average Monthly Average
4/11/2011 -58.88
Apr May Jun
[Code] ........
View 1 Replies
View Related
May 20, 2008
I have an expense ledger that looks essentially like this but spans seven months and counting:
HTML 18-Apr-08pizza$10.00Food
14-Apr-08book$12.50School
13-Apr-08milk $3.99Food
13-Apr-08soap $3.99Grooming
I have been using pivot tables with great success to summarize my data according to categories, payment methods, etc. But now that I want to track monthly spending for each category, it is not working out. Grouping the date fields doesn't work for me for the following reasons:
1. After grouping, the pivot table treats Jan as the oldest month for sorting purposes even though it isn't so in my data set.
2. Based on #1, I believe that the pivot table’s monthly grouping works like the month() function, which returns the month without the year attached. This will not work for my data, which will likely span more than a year eventually.
3. When I choose to group the date fields in one pivot table, they also get grouped in other tables where such grouping is undesirable.
Can I use sumif() or sumproduct() in conjunction with a pivot table or some other way to tabulate my monthly totals?
View 9 Replies
View Related
May 20, 2014
I am trying to do an automatic input with my daily total sales.. i did it in total but my problem is i don't know how to make the date change.
View 2 Replies
View Related
Jan 2, 2009
In column A I have dates, in column B I have data.
What I am trying to do is get the monthly totals from the data so instead of:
01/02/08 - 52
06/02/08 - 87
14/03/08 - 23
12/13/08 - 12
I would get:
Feb 08 - 129
Mar 08 - 33
View 6 Replies
View Related
Mar 13, 2012
I have the following setup:
52 weeks of 2010 cost with the following structure:
week number | wk1 | wk2 | wk3
week ending | 1/10/2010 | 1/17/2010 | 1/24/2010
weekly cost | 57,000 | 35,000 | 42,000
I want to be able to use the above data to present a summary, but instead of weekly, list the data on a monthly basis:
month | Jan | Feb | Mar
monthly cost | wk1-4 | wk5-8 | wk9-12+3days wk13
Any way to set this up automatically instead of having to split the weekly data as many months share the same weeks?
View 2 Replies
View Related
Apr 24, 2013
Using one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
View 3 Replies
View Related
Jun 3, 2007
I have data changes regularly (Say like tank level and we wil asume that at time 2:00 AM is the base , no matter what is the value) and I want to recored the that change every two minutes along with the time as list.
(i.e. if there is no change nothing will be recorded)
View 9 Replies
View Related
Aug 4, 2007
I need get an average call time. I have column C that is the time call started and column D is time call ended. What would I need to get the average call time for the entire sheet? I'll attach a copy of my spreadsheet for you to look at.
View 4 Replies
View Related
Dec 31, 2013
On A3: D10 I have information on C1 a have date and E3:E10 I will input date
i.e. If the date entered in E3 is the same as or larger than the date on C1 then Blank the entire line A3: D3
View 3 Replies
View Related
Dec 6, 2006
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View 9 Replies
View Related
Feb 5, 2014
I have a pivot table with 3 levels of titles in the Row Labels - Grouping, SubGrouping and Name. In many cases there is a Grouping but no SubGrouping and always there is data in the "Name" category. I am trying to eliminate the Subtotal for the blank SubGroupings but when I try to filter out the blanks it no longer shows me any Groupings where a SubGrouping doesn't exist.
View 1 Replies
View Related
Apr 2, 2008
DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?
View 4 Replies
View Related
Aug 13, 2009
I need to alter this code in order to do 2 things
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
View 9 Replies
View Related
Apr 30, 2014
I have only one column (A) with thousands of cell entries. Each Cell contains a date and time. (x/xx/xxxx 12:34:56)
For example, Cell A1 = 1/24/13 18:45:22
I want the new cells to only have the time, but have the seconds removed (and not simply hide the seconds). This is because I'll need to run Conditional Formatting to find every cell with a similar time - but I only need similar times within the same minute...it doesn't matter if the seconds are off.
The new cell should just be A1 = 18:45
The real reason I need this is because I need to find every entry that has the same time (with hour and minutes), which I figure I'd use conditional formatting for. My problem is that many entries have the same time but the seconds are off.
Example:
1/22/13 12:23:11
1/24/13 18:45:22
1/24/13 18:45:36
1/26/13 22:33:41
In this example, I'd want Conditional Formatting to highlight cell A2 and A3 because they have the same time of 18:45 (not including the seconds). I figured the best way to go about this would be to remove the date and remove the seconds, and run the search that way. However, any way to accomplish goal without deleting the date and seconds would be just as useful.
View 4 Replies
View Related
Sep 2, 2007
I need to insert blank rows at the end of the each data segment to add totals for each individual. Is there a macro that I can use for this? I'm attaching an example file.
View 8 Replies
View Related
Jan 17, 2013
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
View 4 Replies
View Related
Jul 8, 2009
I have a macro that needs a major improvement in performance. I have a Quad Core 2.67GHz single processor computer and with the current logic it calculates 20 values per minute. I have data sets that can be up to 400,000 data points, which means it will take 333 hours. The attached Sample file has over 9000 point. For this data set it takes 7.5 hours.
The core logic of the macro is to extract what I call “Break Point” into column M and “Time Difference” between the Break Points to column O.
I got the code from this forum (thank you DonkeyOte) and made some modifications. The modified logic does the following:
1) The user inputs the starting cell. In the Sample I use G200.
2) From cell defined in button, It moves down one cell in that column and compares that value to the original cell.
If the value is greater than original cell then there are 2 possible outcomes:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value to the column M (Break Point value) and copy the Time Difference Value to column O. “Time Difference” is calculated as the difference between the 2 point breaks in Column A. In this case I’ve hardcoded 0.003472222 to get 5 minutes which matches up with each incremental time in column A, but I would like the Macro to automatically calculate that by the difference between Column A values.
b) move down 1 cell in column and if that value is greater than original cell, then move down again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value to column M and copy the Time Difference Value to column O.
Once lesser value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column G.
OR
Here's the logic now in reverse I need:
If after moving down 1 cell of original cell, the value is less than original cell then logic has the following 2 outcomes:
a) move down 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O..
b) move down 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Once greater value has been copied to new cell, logic is now:
a) move down 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to column M and copy the Time Difference Value to column O.
b) move down 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to column M and copy the Time Difference Value to column O.
Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic continues flip flopping like this to the last value of column.
Once all the Break Points and Time Difference between Break Points are extracted for each value in column G, the AVERAGE, STDEV and MAX values of column O are calculated in column Q, R and S.
There is a loop that controls the execution of the core logic until the last value in column G. In the macro I’ve hardcoded last row being 9171, but I’d like the Macro to figure out what the last row is automatically.
I know this description is a bit long but I’ve worked for many hours on getting it work properly. I just need some help to make it run much faster. I hope someone can help me out on this. I have the right logic, just need the speed now and I really can’t figure it out.
Attached is a sample files demonstrating showing how the logic to flips back and forth as moving down the column starting in cell G200.
The sample shows the results first 4 loops (Rows 200 to 203). The values in M and O are the results of the fourth loop.
Again, assistance is very much appreciated because I've taken this as far as I can with my limited experience.
EDIT - I can embed the Macro, but it'll probably much easier to actually see it in the sample file.
View 14 Replies
View Related
