Please help - Unable to Subtract Correct Time

[Ravindran Raman]

Dear All,

I created a format to calculate Overtime calculations but I problem in time subtraction, for example Time In - 07.00 hours to 08.30 hours is 1.5 hour of Overtime but each time I need to input the time as Time In - 07.00 and Time Out - 08.50 to get 1.5 hours.

Tried changing the time format but every time I am getting 00.00 or 7.12.

Please help to relook at the formula and advise where I have gone wrong.

Thank you very much.

Regards,

Ravin

[FDibbins]

Hi, welcome to the forum

What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Wed Sep 2015) is actually 42263

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

If you enter time in teh correct format (08:30 etc), excel will calc correctly. If you enter time as whole numbers (8.3 or 8.5) you will need to do extra calcs to convert it to time

[Ravindran Raman]

Thank you for advise and guidance.

Ravin

Hi, welcome to the forum

What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Wed Sep 2015) is actually 42263

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

If you enter time in teh correct format (08:30 etc), excel will calc correctly. If you enter time as whole numbers (8.3 or 8.5) you will need to do extra calcs to convert it to time

[wator89]

Yes, you should put time in format HH:MM. However I would suggest to put hours and minutes in separate columns and than calculate it with ‘Time’ function. I don’t know how many people will use this file, but it will sure prevent format mistakes.

[Ravindran Raman]

Thank you for your Advise. Ravin

[JohnTopley]

Your times are in date & time format rather than just time: is that required?

Format cells to [h]m if you expect results > 24

To convert time to decimal time (time in A1)

=(A1-INT(A1))*24

[Ravindran Raman]

Thank you For your Advise. Regards, Ravin

[Toonies]

hi I have amended your sheet

Revised OT Format1.xls

format Time in and Time out as

00\:00

example enter 700 this will be displayed as 7:00
712 this will be displayed as 7:12 etc

place the following formula in H9

=IF(COUNT(F9,G9)=2,MAX(0,MIN(TEXT(G9,"00\:00")+(TEXT(F9,"00\:00")>TEXT(G9,"00\:00")))-MAX(TEXT(F9,"00\:00"))),"0")*24

copy down and copy to othr Total hrs columns

remeber it is text not real time

[Ravindran Raman]

Thank you for you Advise. Regards, Ravin

[FDibbins]

You have just posted the same thing 3 times. Did you actually do that, or was it a forum problem?

[Ravindran Raman]

Sorry, Wanted to thank the other guys but didn't know actually I posted the thing.

[JohnTopley]

If your problem has been resolved, could you please mark the thread as SOLVED (See "Thread Tools" at top (first) thread).

Thank you.