Rounding up or down in Excel

[cdhampshire]

Hi everyone,

I have created a spread sheet for employees to log flexible working hours. The problem we have is that they enter their time in in 08.00 format (24hr) and the following formula calculates the time of flexible hours banked.

=TRUNC(P7/60)+(P7-TRUNC(P7/60)*60)/100)

I have data validation set up within the time in / out boxes already which are to the 15 minute marks (ie 08.15, 08.30, 08.45 etc...) but what this means is that someone could foreseeably log 15 mins, 45 mins of flex etc... The rule our company has come up with is to only allow flex increments to 30 minutes or 1 hour (up to a max of 3 hours)

So what I am trying to do is; if a person says they come in at 7:45am and then leaves at 5pm, this means they have worked an extra 15mins but because we only recognise 30 minute intervals, I want the flex sheet to read zero. conversely, if someone puts in 7:15am - 5pm, they would have 45 mins but I want it to round down to the nearest half hour.

in addition to this, I want the daily maximum flex allowance to be capped at 3 hours

Thanks all!

[davsth]

I think you need to add an attachment with a few representative lines to get an answer. it is unclear:

• are the times entered as decimals
• do shifts span midnight
• what about lunch breaks

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[cdhampshire]

Hello, thank you for this.

I have attached the document - does this work?

EDIT - I believe it worked. to answer your questions

1. times are entered using a drop down list box
2. shifts do not span midnight
3. people enter the time they go on lunch and the time they return

[davsth]

Tell us what results you want i a few cells in the sample, im not clear. In the sheet everything is perfect
if I worked 7.30, 8.30, 8.15, 8.15, 8 in a week
what should the results be for accum hours? 40.30
diff hours? 0.30

if so
if I worked 7.15, 8.30, 8.15, 8.15, 8 in a week
what should the results be for accum hours? 40.15
diff hours? 0 even tho I worked 30 minutes over in a day?

is it impossible to work 8.15 if I am behind with my hours to make it up?

when is flexi applied, weekly or at the end of a timesheet?

[cdhampshire]

Hi Davsth, thanks for coming back to me.

The sheet itself works perfectly fine but at the moment, people are able to enter a start time as 7:45am and then finish at say 5:00pm and the spreadsheet would register 15minutes of flexi time. The problem is, we only recognise flex increments of 30 minutes so in the above situation, I would want the flexi sheet to not give minutes, but to give 0 minutes. Conversely, if they were to work 7:15am - 5pm (thereby working an extra 45 minutes) I would only want to spreadsheet to recognise 30 minutes.

Thank you again

[JeteMc]

If I understand the question and spreadsheet correctly then I believe you could resolve both issues by making the following modification to the formula in cell M5 (and copying down):

=MIN(K5+L5-MOD(K5+L5,30)+(TRUNC(F5)*60+((F5-TRUNC(F5))*100)),660)

Let us know if you have any questions.