Free Microsoft Excel 2013 Quick Reference

mileage reimbursement formula

I have a spread sheet I was given to use for mileage reimbursement. They
have one colum where you enter the miles you've traveled, say 230 miles and
the formula is =IF(SUM(H18:H43)>0,SUM(H18:H43),"") the next column is
supposed to total the miles and I believe multiply by .36 per mile but the
formula is broke. What they have is: IF(SUM(I18:I43)*0.36)
can anyone help me figure out this formula?
Thanks,
Cinder


Post your answer or comment

comments powered by Disqus
I have a spread sheet I was given to use for mileage reimbursement. They
have one colum where you enter the miles you've traveled, say 230 miles and
the formula is =IF(SUM(H18:H43)>0,SUM(H18:H43),"") the next column is
supposed to total the miles and I believe multiply by .36 per mile but the
formula is broke. What they have is: IF(SUM(I18:I43)*0.36)
can anyone help me figure out this formula?
Thanks,
Cinder

Hello,

I'm a first timer and would love to have some help with a particular formula. I'm working on an Expense Report in Excel and would like to have a formula in the mileage section so that people can enter the miles they drove and it will automatically calculate. Our current mileage reimbursement rate is 40.5 cents per mile. Does anyone know what formula to use????

Thank you for any assistance!!!!

Sylvie

I am using an Excel spreadsheet for a payroll function that relies on
seperate formulas to calculate personal services and mileage reimbursement.
The two areas are paid with different checks so they need to be seperate.
However, in order to balance there must also be a calculated total. I
formated the cells so 'currency' is used and two decimal points are
displayed. But, when the two cells are summed, they are summed on actual
values and not the displayed rounded values. The problem is obvious . . . you
cannot pay someone $57.125 so it needs to be rounded to a paid amount of
$57.13 . . . but how do I get Excel to sum on $57.13 and not $57.125?

Thanks for any help or suggestions.

Hi,

Having a spot of bother here with a formula, what I need if any one can
help is the following

Sum of a number of fields and IF that SUM is > than 10000 then * by
0.25

This a mileage workout and it's pulling my hair out.

Thanks in Advance

John

--
johndavies
------------------------------------------------------------------------
johndavies's Profile: http://www.excelforum.com/member.php...o&userid=37383
View this thread: http://www.excelforum.com/showthread...hreadid=570693

Hi,

Having a spot of bother here with a formula, what I need if any one can help is the following

Sum of a number of fields and IF that SUM is > than 10000 then * by 0.25

This a mileage workout and it's pulling my hair out.

Thanks in Advance

John

I have created an expense report (attached) and would like to include a calculation/formula that self calculates our current mileage reimbursement amount of .505 when someone enters a number of miles in the 'AMOUNT:' column of F AND selects the 'ACCOUNT CODE' of 7501 from the drop down tab in column I. I would like the total to selp polupate in the 'TOTAL' column of K.

It is currently set up where entries and 'AMOUNT' in Column F self populate and match the "TOTALS' in Column K. and then subsequently to the next page and consolidated according to code(s). I would like to now add the above option ONLY IF someone enters a "NUMBER OF MILES" in Column F and selects '7501' from the dropdown in I without disrupting all other formulas in place.

I am in WAY OVER MY HEAD!!!!

For example, if I put '210' in the AMOUNT Column of F, I want Column K to show "$106.50".

Is this even possible?!? PLEASE HELP!!!!

CCL Expense Report Trial.xlsx

I am sending out emails (Lotus 8.5.2) with Excel spreadsheet attachments (Excel 2003) using vba code (thank you Ron De Bruin). The process has worked great, but now I want to add another spreadsheet column that will contain a formula that I want to be included in the attachment the email recipient receives (i.e., the recipient opens the attachment and enters their vehicle mileage into the spreadsheet and the conditional formula would display an error based on their input). With my current code, the formula does not come across with the attachment, only the actual error message that was in the spreadsheet when it was attached. Confusing, I know. Let me try again. Here is the formula I want to be included in the attachment:

If for example D2 is empty (column C has previous mileage, column D has current mileage), the formula would cause
"ERROR, mileage not reported" to be displayed in E2. The problem is...only the message is what shows up in the
email recipient's attachment (column E) and although it is still an accurate message for the recipient to see, I want the
formula to also be there because the recipient will be asked to enter their mileage into the spreadsheet (and return to us)
and the formula is supposed to alert them with the messages if the mileage they enter is wrong in some way.

Here is the section of my code that does the sending. Was wondering if my problem had to do with the CreateRichTextItem of > Set obAttachment = noDocument.CreateRichTextItem("stAttachment"). I'm a novice at vba code..so any help/direction would be very much appreciated.

Public Sub SendEmail(ByVal pEmail As String, ByVal pEFN As String, ByVal
pNotifType As String, pNotification As String, ByVal pPOC As String)
'Inherits System.Windows.Forms.RichTextBox
  Dim noSession As Object, noDatabase As Object, noDocument As Object
  Dim obAttachment As Object, EmbedObject As Object
  Dim stSubject As Variant, stAttachment As String
  Dim vaRecipient As Variant, vaMsg As Variant
  Dim Password As String
  Dim CurDate
  CurDate = Date
  Dim LDate As String
 
  LDate = (MonthName(DatePart("m", CurDate))) & ", " & DatePart("yyyy", CurDate)
 
  Const EMBED_ATTACHMENT As Long = 1454
  Const stTitle As String = "Active workbook status"x
  Const stMsg As String = "The active workbook must first be  saved " & vbCrLf _
  & "before it can be sent as an attachment."
  Do
    vaRecipient = pEmail
   Loop While vaRecipient = ""
   'If the user has canceled the operation.
   If vaRecipient = False Then Exit Sub
 
     If pNotification = "Final mileage due to Govt" Then
       vaMsg = "Removed for the message board " & LDate & vbCrLf & vbCrLf
 
     ElseIf pNotification = "Gas cutoff notification" Then
       vaMsg = "Removed for the message board" & vbCrLf & vbCrLf
 
     ElseIf pNotification = "Third notice" And pNotifType <> "PM service due notification" Then
       vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _
     Else
       'First and second notices (third notice is above)
     Do
       vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _
 
     Loop While vaMsg = ""
     End If
 
     'If the user has canceled the operation.
     If vaMsg = False Then Exit Sub
     Do
       stSubject = pEFN
     Loop While stSubject = ""
     stAttachment = ActiveWorkbook.FullName
 
     'Instantiate the Lotus Notes COM's  Objects.
     Set noSession = CreateObject("Notes.NotesSession")
     Set noDatabase = noSession.GETDATABASE("", "")
 
     'If Lotus Notes is not open then open the mail-part of it.
     If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
     'Create the e-mail and the attachment.
     Set noDocument = noDatabase.CreateDocument
     Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
 
     Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
 
     'Add values to the created e-mail main properties.
     With noDocument
      .Form = "Memo"
      .SendTo = vaRecipient
      .Subject = stSubject
      .Body = vaMsg
      .SaveMessageOnSend = True
     End With
     'Send the e-mail.
     With noDocument
      .PostedDate = Now()
      On Error GoTo Err_Handle
      .Send 0, vaRecipient
     'Release objects from the memory.
      Set EmbedObject = Nothing
      Set obAttachment = Nothing
      Set noDocument = Nothing
      Set noDatabase = Nothing
      Set noSession = Nothing
 
      Exit Sub
Err_Handle:
      If vaRecipient = " " Then
        MsgBox pPOC & " has no email address in column L of your Master spreadsheet"
      Else
        MsgBox " Either the Group is setup wrong in this program for " & pPOC & " or something else is
going on.  Alert Catherine"
      End If
     End With
 
   'Release objects from the memory.
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
 
End Sub


Okay, this one is too complicated for my wee little brain to comprehend. I get started and completely lose it, so I'm appealing to the experts. See the attached example, ignore the first 29 columns, they're there just to represent the vastness of my original spreadsheet and to allow any formulas to hit the right columns.

High level view, I'm trying to populate columns BL, BM, and BN ("Division", "Purchase/Contract Description", "Vendor/Employee Name"). There's another workbook ("Lookup.xls") with one sheet ("Sheet1") and three columns; "Lookup#", "Div", and "Name", that I use to pull information from.

Here's the part I'm stuck at

In the LBB_ACCT column, if the acct is "L2009", if the "Descr" field entry begins with T1001, or says "Travel", "Travel Exp", "Travel Expense", or says "Reimbursement", "Volunteer Mileage Reimbursement", "Volunteer Reimbursement", or "Volunteer Travel Exp" and the Ln Vendor ID begins with a "2", these all relate to travel. For all of these I'd like to use the last 9 numbers of the Ln Vendor ID to lookup the Div and Name from my Lookup sheet, and put "Travel Expense" in the "Purchase/Contract Description" field. That's the first part.

Now, some of the lookup numbers may not be on my list of numbers. In that case I'd like to pull the name from the LN Vendor Name column, then scan up and down column BN for the same name.' If it's found, pull the Div from that one and paste to the line I'm working on, put "Travel Expense" in column BM. If it's not found, paste the name from the LN Vendor Name column into column BN, put "Travel Expense" in column BM, and put "#N/A" in column BL.

Whew! I know it's a lot to ask. I start thinking about ways to do it all and my mind goes numb, so any help you can provide is greatly appreciated.

Thanks,
John

Hello,

I am trying to create a formula that will calculate mielage totals for our drivers' daily trips. I have attached a spreadsheet for your review. The fields I need totals in are highlighted in yellow.

I ultimately need total miles for each job (line), total WI miles for that day, total IL miles for that day, and total miles for that truck (tab). This is easily doable if our drivers sequentially fill in the cells and leave no blanks, however, that is rarely the case as they go to muliple jobs in multiple states. I have set up two examples, one in green and one in blue.

We need to tack all of this info for job costing and government purposes. Thank you for any help you can provide. We are on 2003.

I work for a company that gives a certain mileage reimbursement to guys
working at certain jobs. I want to know how I can enter the name of the job
and have the dollar amount of the reimbursement show up in the appropriate
column. Thanks!

Excel 2002
I need to format a sheet with several hundred employees, be able to send
this sheet to a supervisor, have him record wages to be paid by date and
mileage reimbursement, with a month total due employee. Have hime send it
back to me and be able to print a sheet for each employee with the totals
listed by date that tehn becomes a voucher for payment.
Thanks

I need a macro solution, and this one is taxing my brain, so I'm appealing to the experts. I've provided examples of two workbooks, "Example.xls" and "Lookup.xls". Ignore the first 29 columns in Example; they represent data unnecessary for this task.

What I'm trying to do is populate columns BL, BM, and BN ("Division", "Purchase/Contract Description", "Vendor/Employee Name") in the "Example" workbook. The other workbook ("Lookup.xls") has three columns; "Lookup#", "Div", and "Name", that I use to pull information from. From that, and from other sources, I fill in the data in Columns BL, BM, and BN. What I can't find results in an #N/A.

Once I'm done, there's cleanup that needs to done for my Travel expenses in L2009 Travel. That's where I'm stuck. There are two tasks I need to accomplish.

The First is: In the LBB_ACCT column, if the acct is "L2009", if the "Descr" field entry begins with "T1001", or says "Travel", "Travel Exp", or "Travel Expense", or says "Reimbursement", "Volunteer Mileage Reimbursement", "Volunteer Reimbursement", or "Volunteer Travel Exp" and the Ln Vendor ID begins with a "2", then the lines are all travel related. For all of these lines I'd like to use the last 9 numbers of the Ln Vendor ID to lookup the Div and Name from my Lookup sheet, and put "Travel Expense" in the "Purchase/Contract Description" field.

The Second is: Some (most) of the lookup numbers for L2009 may not be on my list of numbers. In that case I'd like to pull the name from the LN Vendor Name column, then scan up and down the LN Vendor Name column for the same name. If it's found, pull the Div from column BL for that one and paste to the line I'm working on, put "Travel Expense" in column BM. If it's not found, paste the name from the LN Vendor Name column into column BN, put "Travel Expense" in column BM, and put "#N/A" in column BL.

Whew! I know it's a lot to ask. I start thinking about ways to do it all and my mind goes numb, so any help you can provide is greatly appreciated.

Thanks,
John

I need help with a formula...

I have a spreadsheet that tracks mileage for our fleet. Three times a week (mon, wed, & friday), we get odometer readings from our drivers. The thing is sometimes one or more drivers is off or on vacation, so when there's a blank cell, it throws off the formula, so it has to be manually calculated.

When it works it looks something like this:

Joe Schmo 45888 45891 45905 WEEKLY MILES: 14
[=(d1-c1)+(c1-b1)]

But, if there's a missing number, it looks like this:

Jill Schmo 67900 68566 0 WEEKLY MILES: -67900

Is there a formula that has an AND/OR operator or deals with blanks that I could use instead?

Any help would be much appreciated!

I finally got a list of all zip codes with the Latitude and Longitude of each.

I am looking for an Excel formula I can use to determine mileage between 2
zip codes based on their Latitude and Longitude. I have found several but
have been unable to get them to work in Excel. The following is an example
of what I have to work with.

Zip Latitude Longitude
23219 37.541696 -77.439204 Richmond VA

20315 38.928861 -77.017948 WASHINGTON DC

Any and all help/assistance will be greatly appreciated.

Thanks

I'm working on a travel requirements spreadsheet. I'm quitting my job soon and it will probably be several years before they can find someone qualified to fill my job. My tasks will be divvied up among several people and I'm trying to develop a series of tools (Excel and otherwise) that will allow them to get by in the interim.

One of these items is a travel requirements worksheet. Essentially it's a table of people with relevant information. I have a number of calculations already to calculate various cost estimates. What I need now is essentially to fill the contents of one cell to specify which reimbursement form to complete based off city of residence AND distance. I thought I had a good idea of how to proceed with formulas, but the longer I tried to do the formula the more confused I've gotten.

"Table2" contains the data we're working with (this table may expand or shrink depending on personnel assignments). Multiple columns, but the only columns I'm focused on for this are:
'N' - Where I need the data to fill to (text specific based on other columns); [Header:VOUCHER_VER]
'J' - The city they live in; [Header:ADDRESS_CITY]
'M' - The distance (miles) from their home to permanent work site ("SAN ANTONIO"). [Header:DISTANCE_WS]

What I'm trying to do is get the following result for each individual (which will update if they change address info):

* If greater than or equal to 50 miles ('M'), regardless of city ('J'), then fill, "Zone 2 (Complete Voucher)" in 'N'.
* If less than 50 miles ('M') AND city ('J') is "SAN ANTONIO", then fill, "Zone 1A (Local, No voucher)" in 'N'.
* If less than 50 miles ('M') AND city ('J') is NOT "SAN ANTONIO", then fill, "Zone 1B (Mileage Only, No Voucher)" in 'N'.

Does that make sense? I do not care if it is a formula or requires VBA (I need to expand my horizons, anyway, and VBA might help reduce the chance of them overwriting a formula typed directly in the cell). I also intend to apply conditional (fill) formatting to each row based on which of the three options are filled in 'N' to help them better identify a person's status at-a-glance.

I'm certain this is possible. I'm just apparantly not quite up to the task all by myself.

Hi,

I have attached a file in which i need some help using a formula to calculate a milage rate once it hits a certain level. So in my sheet the limit for mileage is 10,000 at which these can be claimed at 0.45p and anything thereafter at 0.25p. The opening miles is 9800 which means there are 200 miles left at 0.45p and this is reached on the 4th when 30 miles are done in the day - so i need to calculate 20 @0.45p and10 @0.25p. But i need a formula which will say anything which is under 10,000 charge at 0.45p and anything after that threshold at 0.25p.

I hope this makes sense -this is my first post!

Regards

Andrew.Mileage Rate.xlsx

Hi
I'm using Excel to record auto mileage.
I have it set up well enough except for one thing.
Please refer to the screenshot.

Mileage Log example.jpg

Columns A – D are self-explanatory
Column E is the total miles for the day
Column F describes gen'l info for the day.
Cell G 2 is total business miles
Cell H 2 (highlighted in screenshot) is total “rec” or recreation miles

Notice the formula for H 2 –> =SUMIF(F:F,”rec”,E:E)
This means that if I type rec in column F, as in F 3, the miles are considered recreation miles, and therefore are recorded in cell H 3. Conversely, if rec is not typed in, (as in cell F 4), then the resulting mileage goes to cell G 3. So far so good.

The problem is, if I want to type in rec and additional info, eg “rec, went to store”, then the mileage shifts to column G instead of H. ie, The rec miles become business miles.

My question is, how do I amend the formula so it will record “rec” miles, but I can also write other info in the same cell in Column F.

Hope this is clear. Thanks in advance for your help.
M

The following is some help I got trying to figure out a formula to put a cap
on mileage turned in for reimbursement. Our payroll takes the mileage turned
in and looks at the $ amount of production that was turned in by that
individual. If the mileage turned in is greater than what was allowed for
that particular dollar amount of production we lessen the mileage or "cap it"
based on the table below. If the mileage turned in is less than or equal to
what was allowed we do not adjust it. The mileage is pulled off a daily
sheet and talleyed on line $E$2 of the weekly production sheet for each
individual. Every individual has a weekly production sheet in a workbook
where line E2 is mileage and line G47 is production. The process is similar
in all of our locations although the mileage cap varies from site to site.
The following represents one paticular location whereas we may allow 150
miles for the same amount of production in another system. in other systems
we may allow the same amount of miles but base it on less or more production.
My initial thought was to use an Index and Match formula but I couldn't make
it work. When I tried this one it worked except for the ones where the
mileage was less than the cap. This one gives them miles that they didn't
turn in. Unless I'm not understanding and putting the wrong values in it. The
formula written by Mr. Ogilvy was:
=if(or(e3="",e3=0),"",CHOOSE(TRUNC(E3/200)+1,100,200,300,400,500)) The
formula as I tried it
was:=IF(OR('9508'!$E$2="",'9508'!$E$2=0),"",CHOOSE(TRUNC('9508'!$E$2/200)+1,100,200,300,400,500))

Thanks in advance.

Hi, I need some help. I don't even know if this is possible but, I have a mileage/hour log for my partime job. I am using this to keep track of my mileage and hours for invoices. My problem: I have my periods cell, which is for from this week to this week (every 2 weeks usually), set to automatically grab the dates from those selected cells. I would like the Totals to automatically be calculated from those 2 weeks that are in the For Period Cell. Ex:

For Periods: 10/5/2007 - 10/12/2007
Total Mileage: 0
Total Hours: 13
Total Reimbursement: $91.00

Another question: Is it possible, for when I enter an Invoice number, say #1, it will grab the first two weeks for the For Period cell, and do the rest in my first question. Enter #2 grab the second two weeks, enter 3 grab the third two weeks, etc...

Here's a screenshot and I also uploaded it.
Thanks in advance!

http://img99.imageshack.us/img99/291/61150519rv7.png

I am working on a reimbursement spreadsheet for our company's dental plan. I
need to find out how to set it up to pay up to a certain amount. For
example, if somene turns in a claim for $550, and we only pay $400, how do I
set up that formula?
Thanks!

I need to create a formula (maybe an IF statement)
I have a column (COLUMN E) which list either of 2 insurance payors. I have another column (COLUMN F) which lists the sum of charges.
COMPANY A - will pay 70% of the charges (in Column F) if they exceed $150K... if NOT they pay a base reimbursement rate (which is listed in column K).
COMPANY B - will pay 65% of the charges (in Column F) if they exceed $70K... if NOT they pay a base reimbursement rate (which is listed in column K).

so my statement should be:
IF COLUMN A="Company A" and COLUMN F>$150K, then 70%*Total Charge (column F)...IF NOT-just give me COLUMN K (base reimbursement amount)....
OR
IF COLUMN A="Company B" and COLUMN F>$70K, then 65%*Total Charge (column F)...IF NOT-just give me COLUMN K (base reimbursement amount)

I am trying to use excel to calculate cumulative mileage with columns set up in calendar format. My total ends up negative unless all the daily blocks are filled in. What formula could I use to calculate mileage even if all blocks are not filled in?

I am trying to build a spreadsheet that will track the miles and hours of use on our Rental Fleet. Today the drivers post the odometer and clock readings on the log and we manually calculate the miles and hours for the week. I have attempted the formula but end up with a negative cell for the current week until an actual number is posted. I am trying to eliminate the negative cells. I have attached the spreadsheet with detailed comments.

It looks so easy but I can't seem to figure it out. Any help would be appreciated.

Thx,

Tommy

I am almost done with form I have been making and so far this forum has been invaluable.

My last hurdle is this:

I have a list of Tax Exempt Customers that when thier name is entered into the form, I would like it to not add tax.

I have tried a forumula like this:

Code:
The above formula does not work, but I dont know why. I want it to lookup the city tax and add it in if the customer is not
on the tax exempt list. 

Ideas?


No luck finding an answer? You could always try Google.