Military time used in calculating payroll hours

[jzuber]

I have payroll hours in military time. It is in 100th of a minute.
I need to:

enter beginning hours, in military time
enter ending hours, in military time

calculate actual hours elapsed, rounded to the nearest quarter hour


Can anyone help me?

[PaddyD]

=MROUND(b1-a1,1/(24*4))

...where B1 contains end time & a1 start. assumes:

1) entries are held as genuine time values
2) times do not cross midnight, or if they do they are associated with a date value.
3) analysis tool pack is installed (tools | addins)

[jzuber]

I guess I'm more confused than I thought.

How do I enter the military time? What format do I use?

As an example: Beginning = 16:90

Ending = 0:38 -- This will also cross over a date.

I do have add-ins installed, but I'm not sure how this fits into the problem.

Any help is greatly appreciated!

[Yogi Anand]

Hi jzuber:

Military time would be entered in 24 hour format without the need to enter AM or PM. So let us say I start work today (Aug-06-2003) at 16:45 and I work till 00:30 hour. Then my ...

Start Time is entered as 8/6/03 16:45
and
Quit Time is entered as 8/7/03 00:30

I hope this helps. If I have misunderstood your question -- my apologies!

[jzuber]

Hi Yogi:

Thanks for taking the time to help me out. I think the beginning of my confusion is keying the date into excel.

When I enter the time (ie., 15:84) excel automatically associates a date with it .... or tries to. How do I format the column?

Do I enter a date, then the time in the same cell?

As you can see, I'm quite confused.

Thanks again, JAZ

[Yogi Anand]

Hi jzuber:

Excel handles Time as part of the day -- think about it a day has 24 hours, so an hour is 1/24 of a day, and so on.

Now in regard to entering Date and Time ...

Date and Time can be entered separately or together -- but please bear in mind since time is part of a day, whether these are entered together or separately may be a matter of need or preference. The biggest confusion comes in the Formating aspect -- Date and Time can be formated in different ways -- but regardless of how something is formated, its underlying value remains unchanged.

Please look at the following spreadsheet simulation ...

******** ******************** ************************************************************************>

Microsoft Excel - y030806h1.xls ___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D
1	*	*	*	*
2	StartTime	8/6/03*16:45	*	*
3	QuitTime	8/7/03*0:30	*	*
4	*	*	*	*
Sheet6 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In cell B2, I entered the date, then a space and then hours, then a : and then minutes

Does it help?

[Brian from Maui]

Originally Posted by jzuber

Hi Yogi:

Thanks for taking the time to help me out. I think the beginning of my confusion is keying the date into excel.

When I enter the time (ie., 15:84) excel automatically associates a date with it .... or tries to. How do I format the column?

Do I enter a date, then the time in the same cell?

As you can see, I'm quite confused.

Thanks again, JAZ

15:84 is not a valid time in Excel. Are you wanting to convert time to decimals? If so, multiply by 24 and format cell as a number with two decimal places. 15.84 is approx 15:50

Edit:

Didn't see you Yogi, my apologies

[Yogi Anand]

Hey No worries, Brian!

I am hoping that Jaz would actually enter the time based on my simulation -- that is why I mentioned Hours and Minutes separated by : -- so come time to actually making the entries, Jaz will say, Ah! but we don't talk about 84 minutes while talking about clock time in Hours and Minutes.

We all have to pick things up at our own pace!

[jzuber]

I finally understand what I was doing wrong and can see how this will work. I'm still not able to calculate the difference between ending and beginning hours though.

Using 15:84 as my start time, my clock calculates portions of an hour in 100th of a minute, so do I need to convert this to actual minutes (ie., x/60=84/100, which gives me 50.4 minutes) before I can calculate the elapsed time?

I used the MRound formula, but I didn't get anything close to the right time.

I'm really looking for something simple, so I don't want to have to enter the dates and hours, etc. I considered a very long IF statement to convert to normal time, then just subtract the time. Am I heading for the ozone yet?

JAZ

[Yogi Anand]

Hi Jaz:

Let us go systematically -- once we have got the basics pat, it would be smooth sailing. As I mentioned in my posts as well as in my simulation(s), time is shown with hours and then a colon and then minutes, so since there are 60 minutes in an hour, we do not talk of time as being 15:84

Now if you hav time as 15 hour and 84/100 of an hour that is not shown as 15:84, but instead it will be shown as 15.84 hours -- Fair enough! Excel has its own lingo, and if we are going to work with Excel, the sooner we get past these nuances, the better.

So, if you are still with me,

tell me what is the StartTime
whether you tell it to me in Hours and Minutes, or in Hours and decimals of an hour, be clear about it and know the difference

and then tell me what is the QuitTime
again whether you tell it to me in Hours and Minutes, or in Hours and decimals of an hour, be clear about it and know the difference

Once we clearly know what is the QuitTime, and what is the StartTime, how long one actually worked is simply a matter of subtracting the StartTIme from the QuitTime

So, provide the requested information -- and then let us take it from there.