• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula need for calculation [SOLVED]

W

webmax

Member
Hi,

Kindly find the below conditions if they work between these work time the amount should come mentioned below other wise make it as zero.

I need the formula

[pre]
Code: 
Start Time	End Time	AMOUNT
06:00	          07:00  	25.00
06:00	          08:00         50.00
18:00	          22:00          200
18:00	          00:00         225.00
18:00	          02:00         255.00
[/pre]
 
i see some 'problems' with the table/conditions...

if we are looking at a time to see if it is BETWEEN one of the above conditions, then what if the time is 6:30... this is between the first condition and the second??
 
Hi, webmax!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Assuming your data is in worksheet Sheet2 from A1 to C6, try the following formula, but first arrange properly the Start and End ranges, there shouldn't be any overlaps:

=VLOOKUP(<value>,Sheet2!A:C,3,True)


Regards!
 
Hi,


if the value is greater than then the above value should come.


And also how should put vlookup for this because the above has two condition (Start Time & End Time).


Kindly guide
 
Hi Webmax,


Try this it might help you, assuming your data in A1:C6 and your criteria Start and End times in D1 and E1 respectively:


Code: 
=SUM(IF($A$2:$A$6=$D$1,IF($B$2:$B$6=E1,$C$2:$C$6,0),0))


Press Ctrl+Shift+Enter to Execute.


Regards,
 
hi thanks for the formula

if the data it is between the values it is not working for example end time is 07:05 the amount should come as 25 kindly guide


Start Time End Time AMOUNT

06:00 07:00 25.00
 
Hi Webmax,


Little bit confused..


If End time is 7, Ir-respective of 7:05 or 7:59, it should come "25.00"


Am I correct!!!


Regards,

deb
 
Hi Webmax,


I am doubtful regarding your explanation as i think it should be any value between 06:00 to 07:00. Beyond 07:00 or Before 06:00, it violates the Time Bracket we are looking for, see if this is correct.


Regards,
 
Not exactly what you need.. but you may got some idea from the attached..


https://dl.dropboxusercontent.com/u/78831150/Excel/Formula%20for%20Calculation%20%28Webmax%29.xlsx


Code: 
=LARGE(IF((Start_Time>AStartTime-TODAY())*(End_Time<BEndTime-TODAY()),AMOUNT),ROW(A1))


Regards,

Deb
 
hi


It is not working i am uploading the sample workbook link


https://www.dropbox.com/sm/create/sample%20work%20book.xlsx
 
Hi,


When i put the end time as 07:59 it is showing as 08:00 but it should be taken the value of 07:00. and also explain what is 1/48 you have put in the formula
 
Hi ,


Yes , my mistake ; can you clarify whether the start times will also be odd times , or will they always be either 06:00:00 or 18:00:00 ?


Narayan
 
Hi ,


Can you please be specific ; we are spending innumerable posts just clarifying. I will again repeat my question :


Can the Start Times be odd times like 18:37 , 6:42 , or will they be only 6:00 , 18:00 ?


Narayan
 
Hi ,


So , if we have the following times :

[pre]
Code: 
Start Time : 06:47:00

End Time   : 07:05:00
[/pre]
what will the amount be ?


Narayan
 
Hi ,


I don't feel like spending more time on this , since you yourself want to economize on your words ; I'll just give the following formulae , and you can use them as you see fit.


As I see it , you want the Start Times rounded up to the next higher hour or half-hour , and the End Times rounded down to the next lower hour or half-hour. The following formulae seem to do this , to the nearest minute ; if you want the calculations right down to a second , they will fail ; probably others can come up with something better.


1. To round up to the next higher hour :


=MROUND(A1+"0:29:30",1/24)


2. To round up to the next higher half-hour :


=MROUND(A1+"0:14:15",1/48)


3. To round down to the next lower hour :


=MROUND(A1-"0:29:59",1/24)


4. To round down to the next lower half-hour :


=MROUND(A1-"0:14:59",1/48)


where A1 has the time of interest.


Narayan
 
hi narayan,


Thanks for your tip i have done the same with round and sum if formula


Kindy find the below link if it is possible to merge the L & M column in single cell (Which contains formula)


https://www.dropbox.com/s/vukqsk070x4k719/finalworkings.xls?m


Need your advice
 
Hi ,


I really cannot say whether your calculations are correct ; only you can.


With the following times :

[pre]
Code: 
Start Time : 06:47:00

End Time   : 07:05:00
[/pre]
you had mentioned that the amount should be 0 , but your calculations are giving 25.


If you are serious about getting this right , I think you should first list down all the possible times , and then verify your calculations against all those times , before you think of optimizing.


If these calculations pertain to overtime payment , it is a very important application , and any formula that is used should be tested thoroughly for its correctness.


Narayan
 
You must log in or register to reply here.
Back
Top