Re: Function To Round Off Decimals With Running Total
Thanks Craig. That actually worked perfectly! Not sure what I was doing wrong before, but your formulas worked fine.
I do like the idea of doing things more efficiently though and being able to add rows more easily later, so I'll see if I can build upon your idea AAE. Thanks!
Re: Function To Round Off Decimals With Running Total
That fixes the rounding aspect of it for sure, but if I copy that formula to all the POV fields -- the date/mileage fields that are not yet filled in report #VALUE! in the corresponding POV rows and also the same in the total field. This isn't such a big deal for the POV field, but I need the total field to keep a running total as the user enters in each date / mileage amount. Thanks for all of your help though at figuring this out.
I would like to be able to round off the decimals resulted in the POV field on my worksheet and have a running total of the entries listed below. This occurs sometimes when the mileage units have decimals. In this example, the POV values display $1.00, 99 cents, and 93 cents. Their resulting total should be 2.92, but Excel calculates them as $2.93 due to the additional decimals.
The problem is that if I apply an =ROUND function to the formula in the POV field, while it will display a correct answer if all the date and mileage fields are filled in for which the formula has been applied, it will not give a running total in instances where there are empty date/mileage fields, reporting "VALUE!". Is there a way to force Excel to display the running total?
I have attached the worksheet, with the example above entered in. I have included the formula I've been trying to fix on my worksheet on the bottom as well.
Re: Enter Date Without Separators
But won't that change $2.72 into $2.00? My issue is that when I use decimals for my units (like 5.3 miles), it adds up the total using all the decimal points in the POV field, instead of adding it up based on just two decimals. I thought I could get around it by changing the POV cell formula into
=ROUND(IF(E5<>"",E5*E3,""),2)
which works, but only if all the cells with that formula in it are filled it (it displays the #VALUE! error otherwise) and I need a running total regardless of whether the cells are filled or not. I'm going to keep tinkering with it. This is pretty fun actually!
Re: Enter Date Without Separators
Thanks again! That is quite similar to the VBS script I actually used. That worked the best too, since Excel still needs to be able to recognize it as a date in order to look up the corresponding mileage on the table. I've got everything just about perfect now -- it will make my job much easier! I will post a copy of in case anyone wants to use it for a similar task. I just need to work on the running total at the bottom, as Excel is summing it with all the decimal points and I can't get TRUNC or ROUND to work right based on how I've done it, but that's a whole other thread :spin:
If you want to see how I specifically did the date, you'll want to check out the VBS code. Here is the site I gleaned it from: http://www.cpearson.com/excel/DateTimeEntry.htm
Re: Enter Date Without Dashes?
I should have mentioned that this was an extension from a previous post and I needed the date field to not only display in a date format, but I needed it to remain a date field and not a text field, because I was using a worksheet made up for me that actually reads the field to determine a corresponding mileage amount based on the date.
Anyhow, I figured out how to properly format the range command to include as many as I want! Thanks for all of your help everyone! You people on here are geniuses.
Re: Enter Date Without Dashes?
Sorry, I might not have explained myself quite so well.
I want to be able to enter "032404" into the date field and have Excel recognize it as 03/24/04. If I enter 032404 into a cell it recognizes it as 9/18/1988. I'm not so concerned how Excel displays the date after I enter it, so much that it recognizes it as the date I intended.
The script I got works really well and I can modify it for two sets of date cells I want to use, but I can't seem to get it to work for three sets of date cells.
Re: Enter Date Without Dashes?
I found something that works from the date and time entry page mentioned in the help section, but I can only get it to work for two ranges.
It uses this statement:
If Application.Intersect(Target, Range("E2:R2")) Is Nothing Then
I figured out how to add a second set of ranges by adding it in with a comma ("E2:R2","E8:R8",)) but it doesn't seem to work for a third set. How can I include additional ranges in this statement, because otherwise the rest of the script works perfectly.
I'd like to be able to enter a date in excel as just the characters without the dashes and have it correctly recognized -- such as 05142006 changes to 5/14/2006. I have a great deal of data to enter and it would go much faster if I could do it in this format. Is there a VBA script or function of excel that I could use for this? I've tried looking around on here a bit and in the program settings, but I'm stumped. Thanks!
Re: Calculate Mileage For Discrete Date Ranges
You were right. Works like a charm! I had a cell that I'd made with an error in it when I was working in it because I didn't protect my worksheet first. You are a life saver, thanks again!
I'm going to bug you for two more things if it's possible. Then I'm going to buy a book for the next idea I have and try to learn this stuff myself, because there is obviously so much you can do!
Is it possible to do the following:
1. The ability to enter a date as just numbers (i.e. 081308 for 8-13-2008 or even 8132008 or 08132008 would work too) without the dashes and have the program correctly recognize that? It would make entering the dates much more data-entry friendly.
2. Modify the behavior of the cursor by way of the enter key in the worksheet -- so for example move from E2 (date) down to E3 (mileage), and then jump back up F2 (date) down to F3 (mileage) and so on. Optimally, it would automatically "jump down" after completing P3(the last mileage cell) to the next set of date fields starting in cell E8, then follow the same pattern and jump down again to E14 and so on and so forth for as large as my worksheet is. I'm going to lock all the other cells outside of these so that no entry is possible in any other cell.
Re: Calculate Mileage For Discrete Date Ranges
Thank you very much! That worked perfectly. I have one more request -- how would I go about totalling up all of the mileage amounts (cell e6 and any other cells with amounts)based on what was entered by the user? I've expanded the worksheet and thus now cells cells E6:R6, E12:R12, and E18:R18 contain the POV. I thought about using the SUM function, but I don't think you can use that when the fields you are adding up have IF statements in them. Is there a way to accomplish this?
I have a list of mileage rates that correspond with different date ranges. For example the mileage rate for March 19, 2008 – July 31, 2008 was 50.5 cents per mile. I have 11 different mileage rates / date ranges from 2000 onwards.
I would like to devise a way that I could input the date and the number of miles travelled into the spreadsheet, then Excel would check the date entered in order to choose the appropriate mileage rate and calculate the dollar amount based on the # of miles input. Thanks!
EDIT: Here are the date ranges and rates in the event that anyone is interested in putting something like this together. I would be grateful for an example for one or two of the rates though and hopefully could extrapolate from there.
1/1/00 - 3/31/00 - 32.5 cents per mile
4/1/01 - 12/31/01 - 34.5
1/1/02 - 12/31/02 - 36.5
1/1/03 - 12/31/03 - 36
1/1/04 - 12/31/04 - 37.5
1/1/05 - 8/31/05 - 40.5
9/1/05 - 12/31/05 - 48.5
1/1/06 - 1/31/07 - 44.5
2/1/07 - 3/18/08 - 48.5
3/19/08 - 7/31/08 - 50.5
8/1/08 - current - 58.5
