Complex IF Statement

[Vinnie]

I'm trying to upgrade our billable timesheets to auto-calculate using
a prescribed interval scheme.

I've got the following columns:

Start Time
End Time
Time Units

We use a certain set of billable units that are not necessarily
arithmetic.

1 to 6 minutes = .1 hours
7 to 12 minutes = .2
13 to 15 minutes = .25
16 to 18 minutes = .3
19 to 24 minutes = .4
25 to 30 minutes = .5
31 to 36 minutes = .6
37 to 42 minutes = .7
43 to 45 minutes = .75
46 to 48 minutes = .8
49 to 54 minutes = .9
55 to 60 minutes = 1.0

I want to make a formula that (i) probably first converts Start Time
and End time into decimal times and puts them into some other hidden
columns, then (ii) takes the decimal time and converts it into a
billable unit according to the above schedule.

At first I thought it'd be easy, just do ifs for all of the above 12
possibilities, but then I realized that there are 8 hours in a
standard workday, and I would need to prepare for all contingencies,
up to 8.0 hours:

475 to 480 minutes = 8.0 hours

Any help would be greatly appreciated.

Thanks in advance,

Vinnie

 

[Bruce Cooley]

You're going in the right direction. Instead of IFs, could you make a list
of the 96 possibilities within an 8-hour day, and use a Lookup function to
determine the appropriate billing unit?

Bruce Cooley


: I'm trying to upgrade our billable timesheets to auto-calculate using
: a prescribed interval scheme.
:
: I've got the following columns:
:
: Start Time
: End Time
: Time Units
:
: We use a certain set of billable units that are not necessarily
: arithmetic.
:
: 1 to 6 minutes = .1 hours
: 7 to 12 minutes = .2
: 13 to 15 minutes = .25
: 16 to 18 minutes = .3
: 19 to 24 minutes = .4
: 25 to 30 minutes = .5
: 31 to 36 minutes = .6
: 37 to 42 minutes = .7
: 43 to 45 minutes = .75
: 46 to 48 minutes = .8
: 49 to 54 minutes = .9
: 55 to 60 minutes = 1.0
:
: I want to make a formula that (i) probably first converts Start Time
: and End time into decimal times and puts them into some other hidden
: columns, then (ii) takes the decimal time and converts it into a
: billable unit according to the above schedule.
:
: At first I thought it'd be easy, just do ifs for all of the above 12
: possibilities, but then I realized that there are 8 hours in a
: standard workday, and I would need to prepare for all contingencies,
: up to 8.0 hours:
:
: 475 to 480 minutes = 8.0 hours
:
: Any help would be greatly appreciated.
:
: Thanks in advance,
:
: Vinnie

 

[Ron Rosenfeld]

I'm trying to upgrade our billable timesheets to auto-calculate using
a prescribed interval scheme.

I've got the following columns:

Start Time
End Time
Time Units

We use a certain set of billable units that are not necessarily
arithmetic.

1 to 6 minutes = .1 hours
7 to 12 minutes = .2
13 to 15 minutes = .25
16 to 18 minutes = .3
19 to 24 minutes = .4
25 to 30 minutes = .5
31 to 36 minutes = .6
37 to 42 minutes = .7
43 to 45 minutes = .75
46 to 48 minutes = .8
49 to 54 minutes = .9
55 to 60 minutes = 1.0

I want to make a formula that (i) probably first converts Start Time
and End time into decimal times and puts them into some other hidden
columns, then (ii) takes the decimal time and converts it into a
billable unit according to the above schedule.

At first I thought it'd be easy, just do ifs for all of the above 12
possibilities, but then I realized that there are 8 hours in a
standard workday, and I would need to prepare for all contingencies,
up to 8.0 hours:

475 to 480 minutes = 8.0 hours

Any help would be greatly appreciated.

Thanks in advance,

Vinnie

Vinnie,

Try this formula:

=MIN(CEILING((EndTime-StartTime)*24,0.1),CEILING((EndTime-StartTime)*24,0.25))


--ron

 

[Vinnie]

Ronnie,

Vinnie,

Try this formula:

=MIN(CEILING((EndTime-StartTime)*24,0.1),CEILING((EndTime-StartTime)*24,0.25))

Thanks! I tried it out. It works perfectly, except for three
instances. I am not sure why, but check this out

start,start_dec.,end,end_dec., elapsed_correct,elapsed_calced_value
8:00AM,8.00,8:06AM,8.10,0.1,0.2
8:00AM,8.00,8:18AM,8.30,0.3,0.4
8:00AM,8.00,8:48AM,8.80,0.8,0.9


when the time elapsed is
..1, .3, or .8
of an hour
the formula returns +.1.

it's okay when the time interval is
..2, .5

do i have a bad pentium chip?

thanks in advance,

- vinnie

 

[Ron Rosenfeld]

Thanks! I tried it out. It works perfectly, except for three
instances. I am not sure why, but check this out

start,start_dec.,end,end_dec., elapsed_correct,elapsed_calced_value
8:00AM,8.00,8:06AM,8.10,0.1,0.2
8:00AM,8.00,8:18AM,8.30,0.3,0.4
8:00AM,8.00,8:48AM,8.80,0.8,0.9


when the time elapsed is
.1, .3, or .8
of an hour
the formula returns +.1.

it's okay when the time interval is
.2, .5

do i have a bad pentium chip?

thanks in advance,

- vinnie

Vinnie,

First of all, it would have been much easier for me, and also I would have been
less likely to miss this, had you appended this message to me to the original
thread. Now that can be difficult depending on what you are using to access
the NG. I use Agent which has threading capabilities.

In any event, I saw the message and recognized the diminutive.

No you don't have a bad Pentium chip. The problem is that Excel (and most
other spreadsheets) cannot always represent certain numbers exactly in binary.
For example, consider trying to represent 1/3 in base ten notation. No matter
how much precision you use, it will never be exact, but always 0.99999999.....

So, although 8:06 - 8:00 appears to be 0.1, it's really a bit over that. To 15
digits it is 0.100000000000001 And CEILING(0.100000000000001,0.1) = 0.2

When I tested the formula, I didn't use 8AM -- I think I started with 9AM which
doesn't show the problem.

There are several workarounds. One that should work all the time would be to
ROUND the result of the conversion to one decimal place.

So if the EndTime is in G2 and the StartTime in E2, then:

=MIN(CEILING(ROUND((G2-E2)*24,1),0.1),CEILING(ROUND((G2-E2)*24,1),0.25))

should work in all instances.


HTH,

--ron

 

[Ron Rosenfeld]

On 26 Aug 2003 09:18:10 -0700, (e-mail address removed) (Vinnie) wrote:


My first response was incorrect. In view of the 1/4 hour charges, you need to
round to two decimals, not one:

=MIN(CEILING(ROUND((G2-E2)*24,2),0.1),CEILING(ROUND((G2-E2)*24,2),0.25))


--ron