• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time .. [SOLVED]

Just directly subtract one from the other and apply a custom number format. See http://office.microsoft.com/en-nz/excel-help/calculate-the-difference-between-two-times-HP003056108.aspx
 
Dear Jeffrey i tried but its worthless cause the 2nd time formate is in 14:00 PM insted of 2:00 PM. More help please
 
Dawa - can you post a link to a sample workbook? This will make it easier for me to see what the issue is. See http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Dawa

That is just the format

Internally in Excel 2:00pm and 14:00 are the same number


So a simple =A2-A1 will suffice

You need to set the Format of the cell as [h]:mm
 
[pre]
Code: 
6:00 AM	14:00 PM	result	#VALUE!

6:00 AM	2:00 PM		result   8:00
[/pre]

but the program that i am using is extracting the time format as in first row so when i subtract it shows #Value!


sorry for the 2:00 AM and 14:00 PM, i post it wrong...... :)
 
Hi, dawa!


6:00 AM is a valid time (0,25 actually).

14:00 PM is an invalid time, so it's a string value.

2:00 PM is a valid time (0,583333333).

That's why you're getting an error when using a string value representing an invalid date in a subtract operation.


I don't know what program are you referring to but it looks a bit hard to believe that an automatized process retrieved a 14:00 PM value. If so, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Dawa

I assume that:

6:00 AM is in A1

14:00 PM is in B1

Then try: =IF(ISTEXT(B1),TIMEVALUE(LEFT(B1,FIND(" ",B1)))-A1,B1-A1)
 
One more question....... 6:00 AM 18:75 PM, if the normal working hour is 8 hours, now how do i calculte the extra overtime hour he has worked....
 
Its the same related to above, the system i am using is generating these value in Excel format. I just want to find out the difference which you all have provided the solutions. Just want to find out how many extra hours the staff has worked.

Check in time 6:00 AM

Check out time 18:75 PM

Normal working hour is 8:00 hours
 
Hi ,


Assuming that you have 3 named ranges :


Check_In , which has the time value 6:00 AM


Check_Out , which has the text value 18:75 PM


Working_Hours , which has the text value 08:00


the following formula will give the result 4:45


=TIME(LEFT(Check_Out,2),(RIGHT(SUBSTITUTE(Check_Out," PM",""),2))*0.6,0)-Check_In-Working_Hours


Narayan
 
You must log in or register to reply here.
Back
Top