Trying to create a formula that rounds up when its needed to.

Afternoon Guys,

This is my first post, on, well, any forum ever! So be nice!

I'm creating an electronic timesheet and I've hit a brick wall. So far what i have done is create start and end times. I have formatted these to be time (HH:MM) with the range between 00:00 to 23:59.

Some of our employees work over night. I figured when they get to 23:59 on day one, they could go down a row and start day two as 00:00. This of course caused a problem for me as my totals box will now read a minute less, so 0:59.

Because of this I have tried writing a formula that will only round up when the time is over 0:30.

For instance,

If someone entered anything under 0:30 it will not round up. If someone enters anything over 0:30 it will round up to a whole hour. This worked.

I sent the completed timesheet to my boss who decided it would be better to set it to 15 minute increments rather than the previous 30 minutes. I've tried to change the formula to accomodate it but i'm having no joy.

The formula is as follows: -

- =IF(AND(((I10-H10)*24)>0,((I10-H10)*24)<0.16,G10="X"),"0.15",IF(AND(((I10-H10)*24)>0.15,((I10-H10)*24)<0.31,G10="X"),"0.30",IF(AND(((I10-H10)*24)>0.30,((I10-H10)*24)<0.46,G10="x"),"0.45",IF(AND(((I10-H10)*24)>0.45,ROUNDUP(((I10-H10)*24),1),""))

What am I doing wrong? Any help would be appreciated!

Welcome to the forum!

My suggestion is: use the built-in time functions.
A1 = timevalue
B1 = TIME( Hour(A1), Roundup( Minute(A1) / 15, 0) * 15, 0)

This will roundup to the nearest 15 minute increment.

Some of our employees work over night. I figured when they get to 23:59 on day one, they could go down a row and start day two as 00:00. This of course caused a problem for me as my totals box will now read a minute less, so 0:59.

My approach would be to go from time values to date+time in the cell. Then you can subtract one from the next without having to roll over.

Hi Ben,

I don't think i'm familiar with the 'timevalue' function.

I've attached a picture of my timesheet. Would you be so kind as to explain how i could use it?

Thanks again!

Willexcel.JPG

...Ah, I should have been more specific.

I meant that A1 is the time value you want to assess; it's the hh:mm range. Not that you need the TIMEVALUE function.

So for example,
If the user input is
A1 = 4:13
Then the formula would output
B1 = 4:15

For you specifically...
If H column is the start time,
H10 = "start time"
And I column is the end time,
I10 = "end time"
then the time value you need is just I10 - H10

So then if you want column L to have the that amount rounded up by 15 minutes, you would put in:
L10 = TIME( Hour(I10 - H10), Roundup( Minute(I10 - H10) / 15, 0) * 15, 0)


Also...
It's more helpful to us if you attach the XLS you're working with, rather than just a screencap JPG or whatever.

Thanks Ben,

Great help!

I'll be sure to do that next time.

Will

Hi ChemistB,

It makes sense to me. I've inputted it into the relevant cell and nothing :/

What could I be doing wrong here??

Thanks,

Will

Thanks for all your help gents!

Sorry for the late reply.