Timesheet Help. Rounding to the nearest 1/4

J

John

Hi, I been working on this timesheet and I thought I had it right. I was
working on actual time worked but this is not what my employers want.

I am hope you can help. My timesheet has a layout like is:

IN OUT Meal IN OUT Total

What we need is to have the time round to the nearest ¼ hour within each IN
and OUT.

Example:

IN OUT
8:07 AM 11:52 PM

The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with
the output being 3.75 hours.

This would need to be done within the formula (?) and not change the cells
format that the times have been enter but showing the end results in the
TOTAL cell.

Example:

IN OUT Meal IN OUT Total
8:07 AM 11:52 PM 3.75

As you see there is still another IN/OUT. I would need to do the same with
those. Now the way I currently have it setup is when I enter the time in the
IN cells I get no output until I enter the time in the OUT cell. The formula
I am currently using is below.

=ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2)

The “Meal†cell (D15) has no value. It is just for cosmetic.

The end result should look like this.

IN OUT Meal IN OUT Total
8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50


Thanks in advance.
 
R

Richard Buttrey

Hi, I been working on this timesheet and I thought I had it right. I was
working on actual time worked but this is not what my employers want.

I am hope you can help. My timesheet has a layout like is:

IN OUT Meal IN OUT Total

What we need is to have the time round to the nearest ¼ hour within each IN
and OUT.

Example:

IN OUT
8:07 AM 11:52 PM

The 8:07 AM would become 8:00 AM and the 11:52 PM would become 11:45 PM with
the output being 3.75 hours.

This would need to be done within the formula (?) and not change the cells
format that the times have been enter but showing the end results in the
TOTAL cell.

Example:

IN OUT Meal IN OUT Total
8:07 AM 11:52 PM 3.75

As you see there is still another IN/OUT. I would need to do the same with
those. Now the way I currently have it setup is when I enter the time in the
IN cells I get no output until I enter the time in the OUT cell. The formula
I am currently using is below.

=ROUND(IF((OR(B15="",C15="")),0,IF((C15<B15),((C15-B15)*24)+24,(C15-B15)*24))+IF((OR(E15="",F15="")),0,IF((F15<E15),((F15-E15)*24)+24,(F15-E15)*24)),2)

The “Meal” cell (D15) has no value. It is just for cosmetic.

The end result should look like this.

IN OUT Meal IN OUT Total
8:07 AM 11:52 PM 1:12 PM 4:58 PM 7.50


Thanks in advance.

Just a small question first.

How do you get only 3.75 hours from 8.00 am to 11.45 pm? Surely that's
an elapsed time of 15.75 hours?

Or did you mean 8.00 pm to 11.45 pm?

Rgds
Richard Buttrey
__
 
J

John

Sorry, I wrote this in word. This site has no preview before post or edit
after post.

The layout is

IN OUT Meal IN OUT Total

The examples:

IN OUT Meal IN OUT Total
8:07 AM 11:52 AM 3.75


IN OUT Meal IN Out
Total
8:07 AM 11:52 AM 1:12 PM 4:58 PM 7.50

Thanks
 
A

Ardus Petus

Sorry I don't have english version of Excel

In french version, I put the following formula in C6:
=ARRONDI.AU.MULTIPLE(B6;1/24/4)-ARRONDI.AU.MULTIPLE(A6;1/24/4)

This rounds up numbers to the nearest multiple of 2nd argument

The function needs FUNCRES.XLA (Utilitaire d'Analyse)

HTH
 
J

JE McGimpsey

IF I understand you correctly, i.e.:

B C D E F G
1 IN OUT MEAL IN OUT TOTAL
2 8:07 11:52 12:29 16:45

Then

G2 =(ROUND(MOD(C2-B2,1)*96,0)+ROUND(MOD(F2-E2,1)*96,0))/4
 
J

John

Sorry That is 11:52 AM

Richard Buttrey said:
Just a small question first.

How do you get only 3.75 hours from 8.00 am to 11.45 pm? Surely that's
an elapsed time of 15.75 hours?

Or did you mean 8.00 pm to 11.45 pm?

Rgds
Richard Buttrey
__
 
S

Sloth

=(A2<>"")*(B2<>"")*(24*(B2<A2)+(ROUND(B2*96,0)-ROUND(A2*96,0))/4)+(D2<>"")*(E2<>"")*(24*(E2<D2)+(ROUND(E2*96,0)-ROUND(D2*96,0))/4)

Format the total column as general. This formula takes into account times
that go through midnight, and does not subtract the cells unless both have a
value.
 
J

John

Thank you:). Eaxactly what I was looking for.

Sloth said:
=(A2<>"")*(B2<>"")*(24*(B2<A2)+(ROUND(B2*96,0)-ROUND(A2*96,0))/4)+(D2<>"")*(E2<>"")*(24*(E2<D2)+(ROUND(E2*96,0)-ROUND(D2*96,0))/4)

Format the total column as general. This formula takes into account times
that go through midnight, and does not subtract the cells unless both have a
value.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top