Time range formula

Hi AGY, and welcome to the forum.

How are you entering your time values? HH:MM:SS?

Paul thanks for your quick replay

I am not sure if I understand your question. I am not very good with excel

I am entering the values in minutes only up to 1 hour after that I count the hours to for example

if i work between 19 to 24 minutes range I charge .4 of an Hour @150

in a columm example will be

Concept Time amount

Review document .8 $120

So I don't know how to work out this formula to get to the $120 because the range of time is 43 - 48 min = .8 Hour which = $120

I hope this help better

If you're already entering the number .8 into a cell, to get the correct value just use

=150*B1

Where B1 contains .8

Or are you trying to calculate the .8 based on the number of minutes you input?

Yes I am trying to calculate the number basedon the numbers of minutes I input and the amount

If you gave some examples of your "entries" that would help as I think that's what's preventing you from getting a quick resolution.

If you're saying you work 3 hours and 36 minutes and you enter that into say A1 as

3:36

then to get your total you could use something like

=150*SUM(HOUR(A1),(MATCH(MINUTE(A1),{0,1,6,13,19,25,31,36,41,47,53})-1)/10)

would generate 555

change the values in in the line array to match your requirements - you must leave the 0 though... in the above 1-5 would generate 0.1, 6-12 -> 0.2 etc...

It's a shame you need to specify like the above as obviously

=150*(CEILING(A1*24,0.1))

is a lot simpler but in this case would generate 540

Firstly - to avoid confusion - given Paul had outlined an approach for hour.decimal entry I opted to provide an example for hh:mm entry - just in case.

Regards

Originally Posted by darkyam

why would you want it to return $555? 36 minutes = .6 hours, 3.6 * 150 = $540

OP stated

1 to 6 min = .1 (of an Hour)
6 to 12 min = .2
13 to 18 min = .3
19 to 24 min = .4
25 to 30 min = .5

and I assumed the intervals might not be even ie the 1 to 6 and 6 to 12 was unclear given to ceiling of 1 significance the minute break up would be:

1 to 6 = .1
7 to 12 = .2
13 to 18 = .3
19 to 24 = .4
25 to 30 = .5

so perhaps the OP made an error - I wasn't sure... and given OP provided only 5 bands I made up the remainder arbitrarily for sake of demo - to illustrate how a custom minute based calculation could be configured...

If the 1-6/6-12 was an error (ie 1-6,7-12) then yes the bands I provided were inaccurate given they need not be customised but on that basis I also then provided an alternative which used the latter table (which would generate 540 as you mention).

So to surmise, the first example was based upon non-standard minute splits (and people for whatever reason do adopt that approach on occasion), the latter was for standard approach.

Originally Posted by DonkeyOte

Firstly - to avoid confusion - given Paul had outlined an approach for hour.decimal entry I opted to provide an example for hh:mm entry - just in case.

Regards



OP stated



and I assumed the intervals might not be even ie the 1 to 6 and 6 to 12 was unclear given to ceiling of 1 significance the minute break up would be:



so perhaps the OP made an error - I wasn't sure... and given OP provided only 5 bands I made up the remainder arbitrarily for sake of demo - to illustrate how a custom minute based calculation could be configured...

If the 1-6/6-12 was an error (ie 1-6,7-12) then yes the bands I provided were inaccurate given they need not be customised but on that basis I also then provided an alternative which used the latter table (which would generate 540 as you mention).

So to surmise, the first example was based upon non-standard minute splits (and people for whatever reason do adopt that approach on occasion), the latter was for standard approach.

Sorry for my late late repaly. I was out of town. indeed I made a mistake the right number are
1
1-6= .1
7 - 12 =.2
13-18=.3
19-24=.4
25-30=.5
31-36=.6
37-42=.7
43-48=.8
49-54=.9
55-60= 1.0


Now I am a bit confused. which formula I need to use Paul's or D?

First off... I know you know this but I am just posting to try and account for any and all confusion caused by my prior post(s) ...

Originally Posted by d

I was just curious how :36 came out to .7

Via the MATCH.

(MATCH(MINUTE(A1),{0,1,6,13,19,25,31,36,41,47,53})-1)/10)

The MATCH for minute 36 would return 8 from which we must subtract 1 (to account for min 0).
We then divide the adjusted output by 10 to get the decimal.
The decimal is then added to the hour to give us our value against which we multiply the hourly rate.

And to reiterate the above is only a worthwhile approach if you wish to "customise" (from the norm) the minutes apportioned to each 10% of the "hour" - if not then there's zero need for it.

It was purely a random selection designed to be different from the standard apportionment of an hour.

Not offended at all I think I simply failed to elaborate as to the fundamental purpose of the approach.

In retrospect it would probably have made more sense to have picked a really odd subset - say front loaded like:

{0,1,6,11,16,21,26,31,36,41,46}

such that clear 46 min and above is treated as an hour and sub sets of the remaining 45 mins are apportioned accordingly (ie 21-25 mins treated as 0.5)

Obviously the above is a fairly big shift away from the OP so it would have just added yet more confusion