CODEWITHSUNDEEP
excelexcel-formulaformula
AndreasKamper
AndreasKamper

Reputation: 15

Round to hour THEN minute on multiple lines

I have a formumla that based on a service, looks up the rate and then multiplies with the timed used. The problem is, the rate is minimum 1 hour, and then for every minute after that. So I need to always round up to 1 hour, and then round to every minute above 60 minutes.

Where it gets tricky, is that I have 2 services that needs to be summed up. So If service 1 takes 20minutes, and service 2 takes 30 minutes, then I need the total to be 60 minutes on those 2 services.

The sheet looks like this:

Column A - Service: A dropdown of different services

Column B - Start: The start time of the service

Column C - Slut: The end time of the service

Column D - Tidspunkt: Dropdown. Is the service during business hours or not?

Column E - Weight: Dropdown. Weight of the vehicle provided service

Column F - Antal The hours used based on columns C&D OR if the service is not an hourly rate, simply input the number of services provided. Here, the formula as of now is:

=IFERROR(IF(VLOOKUP(A30;Priser!B:G;6;FALSE)="timepris";CEILING(IF((HOUR(C30-B30)*60+MINUTE(C30-B30))/60=0;"";(HOUR(C30-B30)*60+MINUTE(C30-B30))/60);0,5);IF((HOUR(C30-B30)*60+MINUTE(C30-B30))/60=0;"";(HOUR(C30-B30)*60+MINUTE(C30-B30))/60));"")

Column G - Pris: The rate of the service. Formula:

=IFERROR(VLOOKUP(A30;Priser!$B:$G;MATCH(CONCATENATE('1 vogn'!D30;" ";'1 vogn'!E30);Priser!$B$1:$F$1;0);FALSE);"")

Column H - Total: The rate times the hours/#

=IFERROR(G30*F30;"")

Right now, if I put in a time slot, it will always round to the next half hour. I need this to be 60 minutes, and THEN every minute. Also, the data can look like this:

Service 1 - This is based on an minute rate, minimum 60 minutes. Service 2 - This is based on a fixed rate, no matter the time used. Service 3 - This is based on a minute rate, minimum 60 minutes, but should be calculated from the total of Service 1 & 3. Service 4 - This is based on a minute rate, minimum 60 minutes, and is not affected by service 1&3.

Upvotes: 0

Views: 54

Answers (2)

AndreasKamper
AndreasKamper

Reputation: 15

Here is how i ended up I used a fixed row for Service 1

=IF(HOUR(C30-B30)*60+MINUTE(C30-B30)=0;"";HOUR(C30-B30)*60+MINUTE(C30-B30))

A duplicate row for service 2,

And then a row total for service 1 and 2.

=IFERROR(MAX(1;(D30+D32)/60);"")

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You've asked multiple questions and I will respond to what seems to be the major question:

round up to 1 hour, and then round to every minute above 60 minutes.

The time, as a fraction of a day, would be:

=MAX(TIME(1,0,0), MROUND(SUM(rng_to_rnd),TIME(0,1,0)))

or (equivalent formula without using TIME or MROUND):

=MAX(1/24,ROUND(SUM(rng_to_rnd)*1440,0)/1440)
  • To obtain that time in hours, multiply by 24
  • To obtain that time in minutes, multiply by 1440 (i.e: 24*60)

If that is not your question, please clarify. a

Upvotes: 0

Related Questions