24 hour military time sheet help please

1. 24 hour military time sheet help please

Hi please help! I have spent most of today searching on the net and trying to fix this time sheet.

My finance lady needs to SEE the timesheet in 24 hour time.
I need the total time, minus the lunch break calculated, and the answer shown as a number (ie 10 and 30 minutes would look like 10.5, 10 hours and 45 minutes would show as 10.75).

Take a look at this screen shot:
timesheet pic.PNG

I've attached the sheet and you can see it here:

Is there something I am doing wrong here?

Kind regards

2. Re: 24 hour military time sheet help please

One way if you data is truly text. Add formula in F2

Formula:

3. Re: 24 hour military time sheet help please

Hellow Jameshasnobrains, Time calculation always format as Time (h:mm)
Look attach file i have done modification in time i.e. 6:00 / 12:00 / 12:30
Also inseart one "F" column to convert time into decimal.
"G" column formula =(Finish Work - Start Time) - (Lunch Finish - Lunch Start)
In "F" column =(Total Paid Hours)*24 (For showing Decimal)

4. Re: 24 hour military time sheet help please

Looks like your time is not really time, it is text, looking like time. Where is this data coming from?

5. Re: 24 hour military time sheet help please

Wow thanks for your help guys!

I changed the military time format to be: hhmm
This way it is shown in 24 hour format.

Then I combined the formula I was given from you handy guys, with what I already had; =((E3-B3)-(D3-C3))*24 I also made the format to be general.
This is for the totals, this way it is shown as a decimal number, and not a time. For example if I worked 8 hours ad 45 minutes, the daily total would red 8.75.
This is really great because when I calculate my earnings, I just times the totals by the hourly rate.

BRILLIANT!!

Now there is just one tiny problem to fix, and then this will be perfect. Currently i need to insert time by typing like this:

06:00 this will then turn into 0600 with this time sheet.

But inputing all these semicolons ( is really annoying. I am trying to streamline things with this excel sheet as much as I can.

PCMAG says this: "To enter HH:MM time values without the colon, you'll simply use a different custom format. The format 0\:00 will give one- or two-digit hours, while the format 00:\00 will always give you two-digit hours, left-padding with a zero if necessary.

I tried this, but it does not work. Any ideas?

Here's my current xcel sheet if anyone wants a crack at it, or to use it

6. Re: 24 hour military time sheet help please

NEVER MIND I'VE JUST CRACKED IT!!
I feel like we've just discovered something here.
If I format the time cells as text, and then use part of of mike7052's formula (Sorry Mike, yours would not calculate the lunch break properly), which is brilliant btw as we can then calculate text format!

Have a squiz if you want to see this work of art:

=((TEXT(K3,"00\:00")-TEXT(J3,"00\:00"))+(TEXT(M3,"00\:00")-TEXT(L3,"00\:00")))*24

If anybody wants this timesheet, here you. Sharing is caring :D

7. Re: 24 hour military time sheet help please

Hi, yes, I was trying to display what I wanted the timesheet to look like in the first two rows. I found a lot of threads where the time ends up looking like this: 6:45 AM or even 06:45 and not this: 0645

There are currently 1 users browsing this thread. (0 members and 1 guests)