Time Calculation From String

[nigelk]

Hi all,

I've extracted a time from a string i.e, 14:10

and am trying to calculate how many hours/minutes to go from the current time,
so it would 14:10- 13:30, which should equal 40, but an having real problems.

Can anyone help?

Thanks,Nigel

 

[Dryver14]

its probably a question of formatting what do the numbers look like on the sheet?

 

[Aladin Akyurek]

Hi all,

I've extracted a time from a string i.e, 14:10

and am trying to calculate how many hours/minutes to go from the current time,
so it would 14:10- 13:30, which should equal 40, but an having real problems.

Can anyone help?

Thanks,Nigel

What was the string? And what kind of problems are you having?

 

[nigelk]

I've set them both to custom format hh:mm, but as one time is extracted from a string, and the other from a formula :Now(), I'm not sure this is right?

 

[barry houdini]

If you have 14:10 in A1 then you could try

=A1-MOD(NOW(),1)

format result cell as h:mm

If the current time is 13:30 then that will give a result of 0:40.....but what would you want to see if the current time is 14:30?

 

[nigelk]

The string is "Meeting: 14:10"

 

[Dryver14]

Ive just tried this on a sheet and it is not the string that is the problem it is the now() part of the function this generates ### and whenI hover over it it states negative numbers that are too large show as ####

dont know the answer but it is the NOW() at fault

 

[nigelk]

Hi Barry,

If the meeting time is the same as Now(), I would like the result to be 0

(I'd really like to be: "You're late, you're sacked!"), but as it's my boss..........

Thanks,Nigel

 

[barry houdini]

So did my suggestion work?

To give zero if the time has passed then change to

=MAX(0,A1-MOD(NOW(),1))

 

[Aladin Akyurek]

The string is "Meeting: 14:10"

Perhaps something like...

Let A2 house the string.

B2, formatted as h:mm

=REPLACE(A2,1,FIND(" ",A2),"")+0

C2, formatted as h:mm

=MOD(NOW(),1)

D2:

=IF(B2>=C2,"Time to meeting; "&TEXT(B2-C2,"h:mm"),"Late for: "&TEXT(C2-B2,"h:mm"))