I need to be able to calculate the 'overstay' in minutes greater than the below numbers depending on the 'suburb'. If the time is less than the below, for the cell to remain blank.
I can not work out how to get the answer i require using multiple suburbs, with different times.
North Rocks > 45mins
Ingleburn > 30mins
Minto > 30mins
Any other suburb = blank
One way in D3 and copy down.
Please Login or Register to view this content.
Thankyou. However, when i use this, if the mins are less than, its calculating it as a minus. If it is less than, cell needs to remain blank.
Try this in D3:
=IFERROR(LOOKUP(2,1/(A3={"North Rocks";"Ingleburn";"Minto"})/((C3-B3)*1440>{15;30;45}),{15;30;45}),"")
Amended formula,
Please Login or Register to view this content.
Afternoon, thankyou, this has worked. When putting this through to my spreadsheet, i have some times that are moving across to the next day...i.e shift work, 11:30pm - 1:25am. This goes through to weekends as well, so, i can't think how "WORKDAY" would work.
Maybe provide a more realistic set of sample data that shows the issues with the formula as it stands? Make sure that ALL eventualities are covered.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I have attached an updated sheet. I had forgotten about the 'overnight' hours until i was entering the data in, and using the formula that i had been given.
I am unsure how to calculate the hours that are 'overnight', or 'next day'. Times are formatting in the 24hr time.
North Rocks > 45mins
Ingleburn > 30mins
Minto > 30mins
Any other suburb = blank
Why should D9 be 150 mins and not 180?
Is this it?
In E3 copied down:
=IFERROR(IF(B3>C3,1-(B3-C3),C3-B3)*1440-LOOKUP(2,1/(A3={"North Rocks";"Ingleburn";"Minto"})/((IF(B3>C3,1-(B3-C3),C3-B3))*1440>{45;30;30}),{45;30;30}),"")
Please CHECK which version of Excel you have and update your profile accordingly - "I think" is not precise enough!
Or this:
=IFERROR(TEXT(IF(B3>C3,1-(B3-C3),C3-B3)*1440-LOOKUP(2,1/(A3={"North Rocks";"Ingleburn";"Minto"})/((IF(B3>C3,1-(B3-C3),C3-B3))*1440>{45;30;30}),{45;30;30}),"0")&"mins","")
Alternatively,
=IFERROR(TEXT(1/(1/MAX(MOD(C3-B3,1)*24*60-INDEX({30,30,45},MATCH(A3,{"Ingleburn","Minto","North Rocks"},0)),0)),"0 \mi\n\s"),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks