Adding minutes to existing time in a cell

**VKS** · 04-24-2013, 02:04 AM

Dear Experts,
Adding two times in a cell gives answer with date, how can I get rid of this date in the answer cell
Cell A1 contains time 23:50
Cell B1 contains Formula

Formula:

Please Login or Register  to view this content.

When I copy and paste value of Cell B1 in cell C1 it shows 01/01/1900 00:49:00
How can I get rid of this date 01/01/1900 in the formula cell i.e. B1 so that when I copy and paste it in C1 I get to see only 00:49
Best Regards/VKS

**Lemice** · 04-24-2013, 02:08 AM

Hello,

On C1, press Ctrl-1 (Formal Cell), choose "custom", choose or type in hh:mm, and it should show 00:49 only.

**benishiryo** · 04-24-2013, 02:08 AM

hi VKS, you can use:
=MOD(A1+TIME(0,59,0),1)

or just format cells as hh:mm? if format A1, it'll actually show you:
00/01/1900 23:50:00
what you did now is add 59 minutes to 0 Jan 1900. so it becomes 1 Jan 1900.

**VKS** · 04-24-2013, 02:12 AM

Hello Lem, Thanks for your quick response.
Display is showing time only (Custom Format) no date..... but date is showing up in the formula bar and when i use C1 in another formula it gives me error because of the date thats why i want to get rid of date in cell B1. P.S. B1 is also formatted as hh:mm.
Best Regards/VKS

**VKS** · 04-24-2013, 02:20 AM

Thanks a lot Benishiryo. Mod formula worked very well.
You already have my star for your help.
Best Regards/VKS

**Lemice** · 04-24-2013, 02:20 AM

I think banishiryo is right. Time value is always < 1, if you make 23:50 plus an amount of TIME(0,49,0), it will be greater than 1 and be read as 0:39 of Jan 1, 1900. What you can do is subtract 1 from it, format it as hh:mm, and it should be fine.
So what you can do is putting an IF in front of it, if it's > 1 (pass 24 hours / midnight, then subtract 1 from it I guess.