How to get MS Excel to round up times below 30 mins
Discussion
Hi all,
I've been doing some freelance work and recording what I've been doing in a spreadsheet and charging an hourly rate. It's working as is, but I want to charge for a minimum 30-minute block. So if I am asked to do a certain task and it takes 10 minutes, I still want to charge for 30 minutes minimum.
I'm entering start time and end time in adjacent cells, and then the next cell is a simple subtraction to find the difference between start and finish times:
For example The value of C6 is 11:30 and C7 is 12:45, so C8 contains =(C7-C6) and the resulting value of 1:15 shows as expected. But when the time difference is less than 30 minutes, I would like Excel to round up to 0:30. I've found the CEILING function, so that C8 contains =CEILING(C7-C6,1/48) and this kind of works, but it rounds all time values up to the next half hour. So 1:15 is rounded up to 1:30. This would be good for my pocket in the short term, but my customers might not enjoy me charging for 1 hour when I only worked 31 minutes. They are happy for me to charge a minimum of a half hour for smaller tasks.
How can I combine IF and CEILING to only do the rounding if the result is less than 0:30? Any thoughts much appreciated. I do work in IT, but Excel is not my strong suit. I've been fiddling for hours without any success. Thanks!
I've been doing some freelance work and recording what I've been doing in a spreadsheet and charging an hourly rate. It's working as is, but I want to charge for a minimum 30-minute block. So if I am asked to do a certain task and it takes 10 minutes, I still want to charge for 30 minutes minimum.
I'm entering start time and end time in adjacent cells, and then the next cell is a simple subtraction to find the difference between start and finish times:
For example The value of C6 is 11:30 and C7 is 12:45, so C8 contains =(C7-C6) and the resulting value of 1:15 shows as expected. But when the time difference is less than 30 minutes, I would like Excel to round up to 0:30. I've found the CEILING function, so that C8 contains =CEILING(C7-C6,1/48) and this kind of works, but it rounds all time values up to the next half hour. So 1:15 is rounded up to 1:30. This would be good for my pocket in the short term, but my customers might not enjoy me charging for 1 hour when I only worked 31 minutes. They are happy for me to charge a minimum of a half hour for smaller tasks.
How can I combine IF and CEILING to only do the rounding if the result is less than 0:30? Any thoughts much appreciated. I do work in IT, but Excel is not my strong suit. I've been fiddling for hours without any success. Thanks!
mikef said:
=IF(cell<30, 30, cell)
This logically seems to be the answer - I don't really need CEILING at all! However, it doesn't work as is. I've created a column containing "=IF((C20-B20)<30, 30, (C20-B20))" and the result is 30 even though C20-B20=2:40. I suspect it's because the value of my time cells is internally recorded as a decimal number. In my example, I'm testing this in column F:
Macneil said:
Am I underthinking this...not sure of the exact syntax, but imagine a cell C9, with the formula =if(c8<30,30,0) would return 30 as the lowest figure? or a more involved formula in c8, =if((c7-C6)<30,30,(c7-c6)))
?
No I think you're thinking right and I was overthinking. But I still can't get it to work...?
Mr Pointy said:
mikef said:
=IF(cell<30, 30, cell)
I can't get that to work with time formatted cells as anything less than 30 hours is rounded to 30. It works if you convert the Duration to minutes though:
You might have a better solution.
LunarOne said:
What format do you have your duration cells? I tested your way and still can't get it to work, but my duration cells are in Custom [h]:mm format which I need to do my time summing and multiplying by my hourly rate at the bottom or the sheet...
The duration cells are a custom hh:mm format while the Dur Min cells are a number, which is needed for the rounding formula to work. I've added cells showing th eformat & formulas used (note the "" around the formulas should be discarded).
There must be better ways but it's late.
It should be simple enough with a formula, but Excel is notoriously problematic with time functions. If you get stuck load the table into Power Pivot and you can access the Duration functions, which will do a better job.
You can then create a calculated column for the month, and build a Pivot Table into an invoice with a slicer to select the month (assuming monthly) and add a VBA driven button to create an email containing a pdf invoice.
Anyway, bear in mind the difference between using a Format and rounding the time. If you change the format so it shows the right time it doesn't change the underlying numbers, so when you add up all your time items to invoice it will only round it at the end. That works in your clients favour as it treats your time as a single block. You may want that or you may want to round each item up before they are summed.
You can then create a calculated column for the month, and build a Pivot Table into an invoice with a slicer to select the month (assuming monthly) and add a VBA driven button to create an email containing a pdf invoice.
Anyway, bear in mind the difference between using a Format and rounding the time. If you change the format so it shows the right time it doesn't change the underlying numbers, so when you add up all your time items to invoice it will only round it at the end. That works in your clients favour as it treats your time as a single block. You may want that or you may want to round each item up before they are summed.
Ultuous said:
boiler2003 said:
You could use the max function, so C8 would contain:
=MAX(C7-C6,1/48)
Unless I'm missing something, I don't think this got the credit it deserved - looks simple, clean and robust to me!=MAX(C7-C6,1/48)
Ultuous said:
The '1/48' is a half hour (due to a complete day being 1) so it's simply ensuring the returned figure is at least 30 minutes, more than that if the time booked is more, which is what I think you're after!
I was going to comment on that, but as it was in the original formula I didn't. As Ultuous points out Excel stores dates as a number - the integer part is the date (number of days after 1/1/1900 I think) and the decimal part is the time - hence 1/24 will be 1 hour, and 1/48 will be half an hour.LunarOne said:
I haven't had a chance to try it, but I'm struggling to see how it would work? I'll give it a try and report back.
Sorry LunarOne, I could have explained a bit more...The MAX function returns the largest of any of the inputs (there can be more than two, but we only need two in this case). In this case 1/48 represents half an hour, so if C7-C6 is smaller than 1/2 hour, then the function will return 1/2 hour, if C7-C6 is more than 1/2 hour, C7-C6 will be returned.
boiler2003 said:
LunarOne said:
I haven't had a chance to try it, but I'm struggling to see how it would work? I'll give it a try and report back.
Sorry LunarOne, I could have explained a bit more...The MAX function returns the largest of any of the inputs (there can be more than two, but we only need two in this case). In this case 1/48 represents half an hour, so if C7-C6 is smaller than 1/2 hour, then the function will return 1/2 hour, if C7-C6 is more than 1/2 hour, C7-C6 will be returned.
Once I figured out that 24 hours=1 in Excel, I calculated 1/48 in decimal and arrived at the following formula:
=IF((C20-B20)<0.0209, 0.0209, (C20-B20)) which does actually work. I did try with MAX and it didn't work, but now that I understand how Excel works with time, I should be able to find the right format to make it work!
This little exercise has taught me something about Excel and got me a working spreadsheet. I've decided to add another column so that my spreadsheet has a "time worked" column and a "time billed" column for any customers who want a breakdown. This particular client doesn't ask questions and is happy to pay whatever I say my time adds up to. Which makes me think I'm not valuing my time correctly. But that's something I'll have to figure out for myself!
Thank you everyone for your solutions - you've been most helpful! You can see I'm neither a programmer nor an accountant...
LunarOne said:
boiler2003 said:
LunarOne said:
I haven't had a chance to try it, but I'm struggling to see how it would work? I'll give it a try and report back.
Sorry LunarOne, I could have explained a bit more...The MAX function returns the largest of any of the inputs (there can be more than two, but we only need two in this case). In this case 1/48 represents half an hour, so if C7-C6 is smaller than 1/2 hour, then the function will return 1/2 hour, if C7-C6 is more than 1/2 hour, C7-C6 will be returned.
Once I figured out that 24 hours=1 in Excel, I calculated 1/48 in decimal and arrived at the following formula:
=IF((C20-B20)<0.0209, 0.0209, (C20-B20)) which does actually work. I did try with MAX and it didn't work, but now that I understand how Excel works with time, I should be able to find the right format to make it work!
This little exercise has taught me something about Excel and got me a working spreadsheet. I've decided to add another column so that my spreadsheet has a "time worked" column and a "time billed" column for any customers who want a breakdown. This particular client doesn't ask questions and is happy to pay whatever I say my time adds up to. Which makes me think I'm not valuing my time correctly. But that's something I'll have to figure out for myself!
Thank you everyone for your solutions - you've been most helpful! You can see I'm neither a programmer nor an accountant...
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff