# Need Formula Help ~ Converting Military Time in Time Card

1. ## Need Formula Help ~ Converting Military Time in Time Card

Hello~

What is the best way to convert military time so that we don't get a negative number in my total column? Attached is a timecard I am working on for my employer. I have attached the file so you can see what I am talking about. Can anyone help me with formulas to fix these? I am using excel 2003.

We have 2 issues:
1. We need the time card to convert military time for one department (see examples for May 2nd & 3rd). Several overnight shifts for this department.

2. I have listed also examples on April 27th & 28th that do not calculate using regular time Example: when working a Noon shift (no military time). Or a shift that goes from 10am to 5pm.

I have no idea how to fix this so it works!

I hope I have given you enough information!

Jen

2. ## Re: Need Formula Help ~ Converting Military Time in Time Card

I think u can use ABS function...

3. ## Re: Need Formula Help ~ Converting Military Time in Time Card

Perhaps you want:

H7: =MOD(E7-D7,12)+MOD(G7-F7,12)
copied down

4. ## Re: Need Formula Help ~ Converting Military Time in Time Card

Hi Jen

I have posted back an amended copy of your file.
I have only dealt with the May sheet, and I have shown 2 versions side by side.

The first version
All of the relevant cells have been altered in format as Format>Cells>Number>Custom>hh:mm

The sample data has been re-entered using the established time format e.g. 07:00, 11:00, 12:00, 16:00
Where they are Total cells, and the values within them could exceed 24 hours, they have been Custom Formatted as [h]:mm which allows the hours to roll past 24

The formula in cell H7 and down that column has been modified to
=MOD(E7-D7,1)+MOD(G7-F7,1)
This deals correctly with times that cross the midnight threshold, without creating negative numbers.

However, on the right hand side of your Timesheet, where you are allocating, and at the bottom where you want to multiply some values by 1.5, you may prefer to remain in ordinary Numbers and decimal time 8.5 as opposed to 8:30

I have made a copy of your form and pasted it to the right of the existing entries.
The Time input columns D,E,F and G have been formatted as Time (see above).
The remaining cells have been left as Number with 2 decimal places.
The formula in cell Aa7, and down that column, has been modified to
=(MOD(X7-W7,1)+MOD(Z7-Y7,1))*24

This is because Excel stores times as fractions of a day, so to convert to decimal hours we need to multiply by 24.

You can multiply these values directly by value per hour, by overtime rates etc. without problem.

If you decide to stick with all entries being in a Time format, just remember to multiply your formulae by 24.

5. ## Re: Need Formula Help ~ Converting Military Time in Time Card

I think u can use ABS function...

6. ## Re: Need Formula Help ~ Converting Military Time in Time Card

Originally Posted by ContaminatedWitExcel
I think u can use ABS function...
Thank you for responding. I would need some further details as to how to use the ABS function as I have never used it before. I only know excel basics and by no means am I an expert. Could you kindly give me a little more help? And maybe show me how to create the formula that works?

p.s. my excel file is attached to my original post

Thank you!

7. ## Re: Need Formula Help ~ Converting Military Time in Time Card

Ctually I think that my idea is bad in this situation

Roger Govier and DonkeyOte wrote formula you need... which work perfectly

8. ## Re: Need Formula Help ~ Converting Military Time in Time Card

Thank you!!! These are great! Exactly the help I was needing!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1