Time calculation not working past midnight

[Sophie.Durrant]

Hi,

I am working in UK, calling customers in the US so need to calculate the time in the US.

I have a column with the time here now and then for each US state I have the number of hours behind GMT and then the time here minus the number of hours to get the US time.

This was working nicely until about half an hour ago when it went past midnight here, now I am getting #NUM! error. What can I do to fix it please?

I sense there may be a joke in here somewhere to do with turning into a pumpkin

Thanks

Soph

[martindwilson]

well it would help if you actually told us the formula you are using!

[Sophie.Durrant]

sorry it's late!...

I have hours behind GMT as a number in column C, then in column D I have =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())). Then in column E I have =TIME(HOUR($D2)-C2,MINUTE($D2),SECOND($D2))

[daddylonglegs]

Simpler to use this formula in D2

=NOW()-TODAY()

then use this formula in E2

=MOD(D2-C2/24,1)

format both cells in required time format

......or if you want to stick with your current setup you can just add 24 to the HOUR part in the TIME function, e.g.

=TIME(HOUR($D2)-C2+24,MINUTE($D2),SECOND($D2))

[Sophie.Durrant]

That's great, thanks!