Search code examples
exceltimestamp

Excel | Calculating Time Difference (Timestamp using xx.xx.xx instead of xx:xx:xx)

I have 2 timestamps that I need to calculate, SG Time and London Time. They are 8 hrs different, So if user enters SG, it will calculate for London, and Vice versa.

However my users will enter the timestamp in the form of xx.xx.xx instead of xx:xx:xx.

I enter in excel as per how the users enter and convert to Time type, but i am getting error.

I       | J       |   K
Sg Time | LD Time | Diff
10.12.10 | #Value! | 08.00.00

J1 = I1 - K1
Solution

  • In your example only one value is a true time value, the other one is not.

    Format the time entry cells with the time format that is marked with an asterisk. This time format will adjust, based on the user's Windows Control Panel settings for regional time and date format.

    The London user can then enter 9:23:44 and the SG user will see it as 9.23.44 and vice versa.

    The asterisk format can also be found for date formats.

    See this Microsoft Support Article (Format numbers as dates or times) for details.

    enter image description here