Military Time in a timesheet - Using and Calculating

Meghan Baker

unread,

Aug 23, 2002, 5:36:27 PM8/23/02

to

I built a timesheet form that calculates hours worked. Currently, it
works fine if the user inputs the time in hh:mm format, but some of
the users have requested the ability to enter military time (ie.
1300).
How would I format the cells to allow the input of military time (in
c5:c8), and what formula would I use to calculate the total time?

Here is my current time formula:
=((C6-C5)*24)+((C8-C7)*24)
C5 = time in
C6 = time out
C7 = time in
C8 = time out

Thank you in advance!!
Meghan

Daniel.M

unread,

Aug 23, 2002, 6:09:27 PM8/23/02

to

Hi Meghan,

I have the following formula. But there's probably a better solution (i.e.
not involving the Analysis Toolpak):

=DOLLARDE((D6-D5+D8-D7)/100,60)

Regards,

Daniel M.

"Meghan Baker" <ab...@hotmail.com> wrote in message
news:7570f8e3.02082...@posting.google.com...

Daniel.M

unread,

Aug 23, 2002, 6:58:28 PM8/23/02

to

Hi,

Without the Analysis Toolpak:

=INT((D6-D5+D8-D7)/100)+MOD(D6-D5+D8-D7,100)/60

Regards,

Daniel M.

"Daniel.M" <daniel...@bigfoot.com> wrote in message
news:#gQFSEvSCHA.4240@tkmsftngp08...

merlot

unread,

Aug 23, 2002, 8:00:03 PM8/23/02

to

Great formula Daniel... I have tried it. It seems to work well with whole
hours ie 900-1200=3 but if I insert 845 being 0845 then it goes way off
balance.

cheers

"Daniel.M" <daniel...@bigfoot.com> wrote in message

news:O0RarfvSCHA.2556@tkmsftngp09...

Peo Sjoblom

unread,

Aug 23, 2002, 8:38:30 PM8/23/02

to

Chip Pearson has some code here

http://www.cpearson.com/excel/DateTimeEntry.htm

--

Regards,

Peo Sjoblom

Daniel.M

unread,

Aug 24, 2002, 1:45:22 AM8/24/02

to

Appreciate your feedback. Indeed, the second formula (without the Analysis
Toolpak) is wrong.

This array formula (ctrl-shift-enter) seems to work better:

=SUM((INT(C5:C8/100)+MOD(C5:C8,100)/60)*{-1;1;-1;1})

Regards,

Daniel M.

"merlot" <g...@hunterwine.com> wrote in message
news:#SuDQEwSCHA.2024@tkmsftngp08...

Myrna Larson

unread,

Aug 24, 2002, 4:25:44 PM8/24/02

to

I believe you must convert each value into a time BEFORE you do addition and subtraction.

Let's say the times are 0730, 1145, 1230, and 1600. Your formula gives 8.416666 hours.

OTOH, converting each of the 4 values into a time in column E, with the formula

=TIME(INT(D5/100),MOD(D5,100),0)

and applying the formula

=(E6-E5+E8-E7)*24

gives 7.75, which is correct.

I believe the problem with the sample times is that subtracting 1230 from 1600 gives 370. To get
the correct total, that subtraction must yield 330 = 3.5 hours.

The least cumbersome (IMO) formula requires the DOLLARDE function from the ATP, as already
described by another respondent:

=DOLLARDE(C6/100,60)-DOLLARDE(C5/100,60)+DOLLARDE(C8/100,60)-DOLLARDE(C7/100,60)

Daniel.M

unread,

Aug 24, 2002, 7:09:18 PM8/24/02

to

Hi Myrna,

Yep, and so my 2 first formulas were all wrong ;-(

Altough I stand by my last effort ;-)),
i.e. the array formula previously published in this thread (knowing it
capitalizes on the fact the data is in 4 contiguous vertical cells).

Thanks for the heads-up and the info. Have a nice day.

Daniel M.

"Myrna Larson" <myrna...@charter.net> wrote in message
news:qkpfmukuisv61l849...@4ax.com...

Meghan Baker

unread,

Aug 26, 2002, 2:47:56 PM8/26/02

to

This formula seems to be working great!
Was this the formula you were referring to being wrong, or was it
another one? (hard to tell in some of these threads...)

"Daniel.M" <daniel...@bigfoot.com> wrote in message news:<OVwTCDzSCHA.1468@tkmsftngp11>...

> Appreciate your feedback. Indeed, the second formula (without the Analysis
> Toolpak) is wrong.

"Daniel.M" <daniel...@bigfoot.com> wrote in message news:<O0RarfvSCHA.2556@tkmsftngp09>...

Daniel.M

unread,

Aug 26, 2002, 5:26:43 PM8/26/02

to

Hi Meghan,

It doesn't produce the correct results. Try it with the following data :
730
1145
1230
1600

Correct result is 7.75.
Formula produces 8.416666667 as Merlot pointed out.

You want to use either :
=SUM((INT(C5:C8/100)+MOD(C5:C8,100)/60)*{-1;1;-1;1}) ' array formula
(ctrl-shift-enter)

Please also note that if you're using the Analysis Toolpak, the correct
formula (as pointed out by Myrna) is :
=DOLLARDE(C6/100,60)-DOLLARDE(C5/100,60)+
DOLLARDE(C8/100,60)-DOLLARDE(C7/100,60)

Regards,

Daniel M.

"Meghan Baker" <ab...@hotmail.com> wrote in message

news:7570f8e3.0208...@posting.google.com...

Leo Heuser

unread,

Aug 27, 2002, 4:45:29 AM8/27/02

to

Meghan

Or this combination of Myrna's and Daniel's formulae:

=SUMPRODUCT(TIME((C5:C8/100),MOD(C5:C8,100),0)*{-1;1;-1;1})

Not array-entered.

Format the cell as "Time".

--
Best regards
Leo Heuser
MVP Excel

"Meghan Baker" <ab...@hotmail.com> skrev i en meddelelse
news:7570f8e3.02082...@posting.google.com...

Meghan Baker

unread,

Aug 27, 2002, 1:27:06 PM8/27/02

to

I realized that just after I posted the message - oops!
Currently, I'm using the Analysis DataPak with the following
=((DOLLARDE(C6/100,60))-(DOLLARDE(C5/100,60)))+((DOLLARDE(C8/100,60))-(DOLLARDE(C7/100,60)))
Does anyone have suggestions for a formula that works without the
Analysis ToolPak? I'm trying to make this a simple as possible for the
users.

Thanks!
Meghan

"Daniel.M" <daniel...@bigfoot.com> wrote in message news:<#AOWUaUTCHA.3736@tkmsftngp11>...