Cant get the minutes in computing this time stamp

[ragnakistah]

Good day, i need help in getting the timestamp in this xls file. I am having a trouble with it,

I already format the cell and it is now d/m/yy h:mm
I already try this formula =TEXT(F5-D5,"h:mm") and also =(F5-D5)

Please see attached file for more details.

[newbie4]

in cell G5, type =F5-D5, then right click>>format cells
Time should be in 13:30:55 format.
Then click ok....It should now give you what you are looking for, which is 0:02:14

[ragnakistah]

Hello newbie4

I tried what you said but still not working.

\1

[Pete_UK]

Your date/times are text values, so you need to do this:

=TEXT(SUBSTITUTE(F5," AM","")-SUBSTITUTE(D5," AM",""),"h:mm")

Hope this helps.

Pete

[daddylonglegs]

Are you using US regional settings? If not then you may get #VALUE! error if dd/mm/yyyy is used as default date format....because 10/22/2013 would be an invalid date......

[AlKey]

Try this:

=TEXT(TEXT(F5,"h:mm:ss")-(TEXT(D5,"h:mm:ss")),"h:mm:ss")

[ragnakistah]

Are you using US regional settings? If not then you may get #VALUE! error if dd/mm/yyyy is used as default date format....because 10/22/2013 would be an invalid date......

Hello Daddylonglegs,

You just give me a tip, i remember I customized my Regional Settings before I now use the valid date format in my regional settings and it worked like charms! Thanks a lot!

Best Regards,
Ragnakistah

[ragnakistah]

Try this:

=TEXT(TEXT(F5,"h:mm:ss")-(TEXT(D5,"h:mm:ss")),"h:mm:ss")

Hello AlKey!

This formula also works! Thank you!

[ragnakistah]

Your date/times are text values, so you need to do this:

=TEXT(SUBSTITUTE(F5," AM","")-SUBSTITUTE(D5," AM",""),"h:mm")

Hope this helps.

Pete

Hello Pete,

Thank you your solution also works for me! It is much shorter and look great also!

Best Regards,
Ragnakistah

Thank you everyone for your help!

[Pete_UK]

Well, you have a number of working solutions.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[ragnakistah]

Well, you have a number of working solutions.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Hello Pete,

Message well noted!

Thank you!