In article <te5ikg$3c0sn$
1@dont-email.me>, Claus Busch wrote...
Hi Katherine,
Am Wed, 24 Aug 2022 08:43:24 -0700 (PDT) schrieb Katherine Willis:
I'm building a timesheet form that calculates hours worked. The users would like to enter military time (ie. 1300).
How would I format the cells to allow the input of military time (in W10:W11), and what formula would I use to calculate the total time?
W10= time in
W11 = time out
format the cells "0000"
and calculate the duration with: =MOD(TIME(INT(W11/100),MOD(W11,100),)-TIME(INT(W10/100),MOD(W10,100),),1)
Regards
Claus B.
You can avoid a lot of unnecessary complexity by sticking to Excel's native representation of date/time, which is essentially an integer (day value) and a fractional part (time) formatted to display as expected using Excel's built-in formatting options.
If you open a new sheet and enter (e.g.) '1305', it'll be displayed by default as a number (1305!). If (without doing anything different otherwise) you enter '13:05' then Excel will recognise it as a "time" and display it accordingly.
If you put a reference to these two cells in another column (e.g. =A1) and format those cells as 'number' (remember Ctrl-1 brings up the number format dialogue) then you'll see they are very different numbers being stored. If you fool around with the Data Validation options and set them to 'time' with a range of '0:00' to '23:59' the cell you entered without the colon (:) will fail (as shown if you set Excel to ring invalid entries). Unless you store dates and times as Excel provides, you're really swimming against the tide. Sure, for some calculations you will need to use INT and MOD to be able to calculate the day value and time values separately, but in most situations Excel will help you with a range of date/time functions which will make life easier.
Of course, you may need to train your users to enter time values with a colon, but if they can't do that, do you really want them messing with your data from their keyboards? :-)
It may be worth looking into using a form to control and validate the data they can enter. I've much more experience of this in Access than in Excel, but this video will get you started:
https://www.youtube.com/watch?v=cNpFB8M1-dI
--
Phil, London
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)