Time Format

[yclhk]

I'd like to create a excel file to calculate the hours worked.
Col A Col B Col C Col D
Date Time IN Time OUT hrs worked

in col B, record Time IN 7:53am
in col C, record Time OUT 6:05pm
However, 7:53am expects to count as 8:00am &
6:05pm count as 6:00pm, and hours worked results as 10 hours

I wish to keep the records of the Time IN & OUT in the actual time and
how to set the formula to calculate in form of the expected result in col D ?

 

[David Biddulph]

As with so many posts on this group, you haven't included enough detail in
the question, nor does your example make it clear.
How do you do the rounding at each end of the working day? To units of an
hour, or half an hour, or a quarter of an hour?
Do you round to the nearest unit, or does the employee always lose out on
the rounding at each end of the working day?
Do you want a result as an Excel time, or as decimal hours? [In other
words, would you want 8:30 or 8.5?]
Can the time worked span midnight?
Do you need the formula to return a blank in cases where you haven't yet
filled in times for IN and OUT?

Once you've sorted out the question, the answer will probably be relatively
easy.
Making a guess at some of the answers, you may want something like:
=IF(OR(B2="",C2=""),"",FLOOR(C2*24,0.25)-CEILING(B2*24,0.25))

 

[Niek Otten]

=MROUND(C1,15/24/60)-MROUND(B1,15/24/60)

Change both "15"s to any number of minutes you want to round to.

If you get a #NAME error: Tools>Add-ins, check Analysis Toolpak

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'd like to create a excel file to calculate the hours worked.
| Col A Col B Col C Col D
| Date Time IN Time OUT hrs worked
|
| in col B, record Time IN 7:53am
| in col C, record Time OUT 6:05pm
| However, 7:53am expects to count as 8:00am &
| 6:05pm count as 6:00pm, and hours worked results as 10 hours
|
| I wish to keep the records of the Time IN & OUT in the actual time and
| how to set the formula to calculate in form of the expected result in col D ?

 

[Niek Otten]

And format as time

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| =MROUND(C1,15/24/60)-MROUND(B1,15/24/60)
|
| Change both "15"s to any number of minutes you want to round to.
|
| If you get a #NAME error: Tools>Add-ins, check Analysis Toolpak
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| I'd like to create a excel file to calculate the hours worked.
|| Col A Col B Col C Col D
|| Date Time IN Time OUT hrs worked
||
|| in col B, record Time IN 7:53am
|| in col C, record Time OUT 6:05pm
|| However, 7:53am expects to count as 8:00am &
|| 6:05pm count as 6:00pm, and hours worked results as 10 hours
||
|| I wish to keep the records of the Time IN & OUT in the actual time and
|| how to set the formula to calculate in form of the expected result in col D ?
|
|

 

[Ron Rosenfeld]

I'd like to create a excel file to calculate the hours worked.
Col A Col B Col C Col D
Date Time IN Time OUT hrs worked

in col B, record Time IN 7:53am
in col C, record Time OUT 6:05pm
However, 7:53am expects to count as 8:00am &
6:05pm count as 6:00pm, and hours worked results as 10 hours

I wish to keep the records of the Time IN & OUT in the actual time and
how to set the formula to calculate in form of the expected result in col D ?

Assuming you want to round to the nearest 1/4 hour, use one of the following
formulas, depending on whether or not you have the Analysis Tool Pak installed.

If you want to round to the nearest hour, or 30 minutes, change the "15" in the
formula to 30 or 60.

=ROUND(Time_OUT/TIME(0,15,0),0)*TIME(0,15,0)-ROUND(Time_IN/TIME(0,15,0),0)*TIME(0,15,0)

=MROUND(Time_OUT,TIME(,15,))-MROUND(Time_IN,TIME(,15,))

The formulas assume that there will be no work hours that span midnight, but
they can be altered to take that into account if necessary.

FORMAT the result as [h]:mm (or similar)
--ron

 

[Suleman Peerzade]

Hi,

This is quite late to reply but you can still try this out.
A B C D
Date Time In Time Out Hrs Worked
dd/mm/yy 7.53 18.05 10.52

Since excel stores time in fractions the calculation above comes to,
assuming that the data starts from column A.
=(C3/24-B3/24)*24

That is (7.53 am /24 - 6.05 pm /24)*24 = 10.52 hrs

Only one thing make sure that the cells are formatted in numbers and the
data is entered in 00.00 hrs.

 

[yclhk]

Thanks for all,

However, your method would help to make easy to enter the time. Enter 7.53
is more easy to enter 7:53. But, what I want is the formula to count 7.53 as
8.00am and 18.05 as 6.00pm, and with the result of 10.00 hrs worked.

Thanks a lot,

 

[Suleman Peerzade]

HI,

You can probably round number to the nearest Zero (Assuming that you have
entered the data in number format).
for eg.
Date Time In Rounded Time Time Out Rounded Time Hrs Worked
dd/mm/yy 7.53 8.00 18.05 18.00 10.00

you can enter the data in number formate, add one more column besides both
time in and time out, you can name them as rounded time in and rounded time
out.
The formula for rounding would be.
=ROUND(D32,0)

and the total hrs worked formula can be changed accordingly to give you 10
hrs (Consider the rounded columns).

=(G32/24-E32/24)*24