Calculate difference between two times

[CrazyBandit]

Hello. I have a text string that represents a date and time that an employee has signed in. It is a long text string
[TABLE="width: 152"]
<tbody>[TR]
[TD="align: right"]

(20170102205400) which represents 8:54:00PM 01/02/2017 I then have another time, punch out time:
[TABLE="width: 153"]
<tbody>[TR]
[TD](20170103070200)
I tried using the TIMEVALUE formula, and that did not work. Any ideas how I am to make a calculation of how many hours the employee has worked. NOTE: Sometimes they work overlapping days.

Any help is greatly appreciated.
[/TD]
[/TR]
</tbody>[/TABLE]
​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

To convert your string into an Excel serial time, you could use the formula

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2)+TIME(MID(A1,9,2), MID(A1,11,2), MID(A1,13,2)

Then the two times can be subtracted.

Then, to convert back to your format,
TEXT(C1,"yyyymmddhhmmss")

Or you could use a custom format of yyyymmddhhmmss throughout and leave the entries as Excel serial time rather than strings.

 

[CrazyBandit]

Thanks for the reply Mike. When I use the above formula, it brings the correct date back, however, the time is always 12:00AM. If I split up the Date and Time formulas then that returns the correct times but then I cn not calculate between the two time correctly because some employees work nights and will work form 01022017 at 11pm - 7am the following day.

Any Ideas?

 

[Tetra201]

The following formula will covert your text string into an Excel date/time value (that is, a number).
Then, you can subtract one converted number from the other. The difference multiplied by 24 will give you hours.

=--TEXT(-(--A1),"0000-00-00 00\:00\:00")

 

[CrazyBandit]

Thank You @Tetra201

 

[Tetra201]

You are most welcome.