Shift time in 100 minutes (calculate)

[mcc]

Need to calculate the time worked sometimes between 2 days, and it is military time in 100 minutes. Examples:

In
5/19/09 06:00 (which is 6:00 am)
Out
5/19/09 16:50 (which is 4:30 pm)

In
5/19/09 18:10 (which is 6:07 pm)
Out
5/20/09 06:25 (which is 6:15 am)

Then I want to take out .50 (or 30 minutes) for lunch period as well.

I've tried multiple formulas and searched, and can't get it to work. Any ideas?

 

[barry houdini]

Hello mcc,

Why do you need to use that format? If you do then all your calculations will be much more complex than they need to be. If you want 6:07 PM then why not enter it like that, or as 18:07, Both are valid time formats in Excel.

How would you represent 6:45 PM, presumably that would be 18:75 which, because it isn't a valid time, would be regarded as text, causing further complications.....

If you enter the times as "True" times then you can just subtarct the end time/date from the start time/date to get the difference, e.g.

=B2-A2

format result cell as [h]:mm

To take out ½ hour for lunch just use

=B2-A2-"0:30"

 

[mcc]

The time clock is for 20+ employees and I want to be able to calculate the time worked correctly. Unfortunately, the time clock is in 100 minute hours.

Example:

In punch
5/19/09 18:50
Out punch
5/20/09 06:25

I want to be able to input those times in excel exactly like the time clock and have it calculate the person worked 11:45 hours. Because 18:50 is 6:30 pm and 06:25 is 6:15 am; however, excel is calculating it as 11:35 hours because it is thinking 18:50 is 6:50 pm and 06:25 is 6:25 am.

And it would be easy if the times all fell on 1/4 hours, but they do not. Then we have to take out 1/2 hour for lunch or .50 with these time cards.

 

[wsjackman]

does the input from the time clock come in as text?
or do you enter it manually?
what does Excel show for an input like 10:75

 

[mcc]

I can input it any way in excel. I'm wanting to create a spreadsheet according to the time cards. it's the 2 different days that seems to make it very difficult. Ideas?