473,554 Members | 2,877 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

convert the excel formula into a Access 2010 expression

3 New Member
I created a table that has a field name InTime and a field name OutTime with a calculated field name TotalTime. Now I need to create a calculated field name TotalUnits.
I need to convert the excel formula into a Access expression. I’m including the numbers of minutes that equal to a unit (if this helps).

Number of Minutes in Military Time Units
0.13 minutes through 0.37 minutes = 1 unit
0.38 minutes through 0.62 minutes = 2 units
0.63 minutes through 0.87 minutes = 3 units
0.88 minutes through 1.07 minutes = 4 units
1.08 minutes through 1.37 minutes = 5 units
1.38 minutes through 1.62 minutes = 6 units
1.63 minutes through 1.87 minutes = 7 units
1.88 minutes through 2.13 minutes = 8 units

This is the excel formual:
=IF(I8>1.88,"8" ,IF(I8>1.63,"7" ,IF(I8>1.38,"6" ,IF(I8>1.13,"5" ,IF(I8>0.88,"4" ,IF(I8>0.63,"3" ,IF(I8>0.38,"2" ,IF(I8>0.12,"1" ,"0"))))))))
Dec 20 '10 #1
10 5131
8,834 Recognized Expert Expert
Expand|Select|Wrap|Line Numbers
  1. =IIf(I8 > 1.88, "8", IIf(I8 > 1.63, "7", IIf(I8 > 1.38, "6", _
  2.      IIf(I8 > 1.08, "5", IIf(I8 > 0.88, "4", IIf(I8 > 0.63, 3, _
  3.      IIf(I8 > 0.38, 2, IIf(I8 > 0.12, "1", "0"))))))))
Dec 21 '10 #2
3 New Member
Thank you for your quick response, I’ve tried the expression with no results. I keep getting the following error: The expression could not be saved because its result type, such as binary or NULL, is not supported by the server. I have no idea what that means.

I did change the [I8] to the field name TotalTime but it didn’t work. Am I doing something wrong?
Dec 21 '10 #3
8,834 Recognized Expert Expert
Post all the SQL that you have so far.
Dec 21 '10 #4
21 New Member
To see if I understand--you are running an update query in access, or are you working in excel with linked data?

If you are working in access, you can use the IIF function in a nested way to account for each group (be sure to include the name of the field name in access and not the cell reference from excel), but I often find it is easier to insert a module and go with the select case statement , like so:

Expand|Select|Wrap|Line Numbers
  1. Function group(minutes As Double) As Integer
  3. Select Case minutes
  4.     Case 0.13 To 0.37
  5.         group = 1
  6.     Case 0.3700001 To 0.62
  7.         group = 2
  8.     Case 0.6200001 To 0.87
  9.         group = 3
  10.     Case 0.8700001 To 1.07
  11.         group = 4
  12.     Case 1.0700001 To 1.37
  13.         group = 5
  14.     Case 1.3700001 To 1.62
  15.         group = 6
  16.     Case 1.6200001 To 1.87
  17.         group = 7
  18.     Case 1.8700001 To 2.13
  19.         group = 8
  21. End Select
  22. End Function
This function can then be called from the update query interface like so:
Expand|Select|Wrap|Line Numbers
  1. update tblSource set TotalUnits=group(time);
Dec 21 '10 #5
12,516 Recognized Expert Moderator MVP
If I am understanding the math correctly, can't you just use units = Int((minutes - 0.13) / 0.24) + 1
Dec 21 '10 #6
8,834 Recognized Expert Expert
@Rabbit - Very interesting approach, what is the logic behind it?
Dec 22 '10 #7
12,516 Recognized Expert Moderator MVP
I had only taken a cursory glance at the description before coming up with my formulization. I had originally thought the numbers were discrete but it turns out that is not the case. The formula then, according to the Excel formula should be units = Int((time + 0.12) / 0.25) because each unit block is 0.25 minutes long starting at 0.13. Well... I say 0.13 even though the Excel formula says 0.12 because it seems to be a discrepancy with the rest of the formula. If it really does start with 0.12 then the formula doesn't work so I'm hoping that was a typo.

However, if the formula is correct, then it may result in negative numbers and numbers larger than 8, which may be something the OP doesn't want.
Dec 22 '10 #8
8,834 Recognized Expert Expert
Bear with me, Rabbit. Don't the following Unit Blocks negate the assumption?
Expand|Select|Wrap|Line Numbers
  1. '0.88 minutes through 1.07 minutes = 4 units - {19 Minutes}
  2. '1.08 minutes through 1.37 minutes = 5 units - {29 Minutes}
Dec 22 '10 #9
12,516 Recognized Expert Moderator MVP
That's because he typed it wrong. In his formula, it's 1.13 and not 1.08. In that case it works. The only time it doesn't work is that in his formula, it starts with 0.12 and not 0.13. I'm hoping that too was a typo and he meant to start with 0.13.
Dec 22 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: tag | last post by:
Hi, I have a problem in that I need to be able to parse excel formula's and evaluate them to get results. Python is very well suited for this as it has WONDERFUL data handling capabilities. My question is : Is there an excel formula parser out there? and if not - does anyone have an idea on where to start ?
by: alphaomega3 | last post by:
I have a formula used in Excel that I am trying to convert for use in a query. the original formula is: =IF(AND(#REF!>=0,#REF!<=66800),(#REF!*((0.7-1)/(66800-0))+1),IF(AND(#REF!>66800,#REF!<=500000),((#REF!*((0.4-0.7)/(308000-66800))+0.783085)),0)) I have attempted the following but keep getting syntax errors..... tell me what I am doing...
by: skiddle | last post by:
I've got an Excel formula that calculates the total business hours that a help desk ticket is open. As one would expect, it factors out holidays and non-work hours. What would be the corresponding formula in Access? ...
by: welshkaiboy | last post by:
Due to the size of the data I have to manipulate I need to apply a excel formula in access which determines date of manufacture from a serial number 716001 so I use =VALUE(DATE(VALUE(MID(A1,3,1))+2000,1,1))+VALUE(MID(A1,4,3))-1 which gives 1/1/2006 How can I get this formula to work in access I know that the field name replaces the cell...
by: rtilson | last post by:
I am not sure why excel and access use the same method in formula. I am not access expert but been trying to figure it out. Here is the excel formula that I would like to use in access expression =ROUND((A2-5148000)*1.000440935+48000,IF(MOD(A2,1)=0,0,3)) I have tried using is the coorinate
by: phill86 | last post by:
Hi, I have an access 2010 database that I want to convert to an SQL Server database and I need to know if the data macros in access will still work in the SQL database or will i have to create triggers in the SQL Sever database Many Thanks Phill
by: Lina Arraiz | last post by:
I'm trying to export an Excel 2003 spreadsheet to Access 2007. I need to calculate the time elapsed between column D (START) and column E (END) with the results appearing in column F (TIME). The Excel formula, which is placed in column F, goes like this: =24*(IF(E2>D2,D2-E2)) Exactly what language do I use in Access and where do I put it in...
by: david sherman | last post by:
Does software converting excel to access with financial functions exist? I need to take several fixed income portfolio run manually from excel and create a database. The excel relies heavily on excel functions such as yield formula and durations formula. Any software that assists in conversion since i am not an access programmer?
by: Geraldpauig | last post by:
Hi there, need help in converting the excel fomulas to ms access. =IF(C2<=0,IF(SUM(B$2:B3)<F$2,0,SUM(B:B)-F$2),"") below is the data from query1 and query2 QUERY1 COLUMN A COLUMN B COLUMN C DATE Purchased Available cell2 September 30, 2010 50 0 cell3 December 31, 2010 100 172* cell4 January...
by: Sabrina Smith | last post by:
I am not a programmer - so I don't know code, however, I do need some help if anyone can oblige me... I have a project that requires me to add 4 workdays (excluding holidays) to a beginning date for numerous employee entries. The Workday formula in Excel is perfect for what I need, but how can I make it work in Access 2003?
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.