Adding d h:mm to =NOW()

[Mr.GfCs]

:Hi,
I have a cell with formatted with "mmm/d h:mm". The value in the cell is "=NOW()". It provides the value of ... Nov/30 19:30 'cell R26'

The next cell is formatted with "d h:mm". It's value is 17 2:33 (17 days, 2hrs, and 44 minutes.) 'cell R27'

The next cell value R28=R26+R27, with the cell format of "m/d h:mm". It doesn't matter how I format it, it always returns a #VALUE! error.

[MrShorty]

The value in "R27" (appears to be T26 in sample file) is the text literal "17 2:33" which has no numeric meaning (yet) to the spreadsheet. Excel does not know how to add a number (the =NOW() function) and a text string, so you get a #Value error.

You need to enter the correct number value into T26 -- 17.10625. However you want to enter that number will work. You could enter =17+TIME(2,33,0) or 17 Jan 1900 2:33:00 or =17+CONVERT(2,"hr","day")+CONVERT(33,"min","day") or any other entry method that will work to get the correct value in T26. Once you have the correct number in T26, the addition in V26 will be automatic.

[hrlngrv]

The V26 formula needs to be

=R26+LEFT(T26,FIND(" ",T26))+REPLACE(T26,1,FIND(" ",T26),"")

because T26 is text rather than a numeric date+time.

[Mr.GfCs]

That fixed it. thanks. However, the T26 cells format is 'd h:mm'. it's combined with U26..... but I pasted what you said and it works.

Thanks

The V26 formula needs to be

=R26+LEFT(T26,FIND(" ",T26))+REPLACE(T26,1,FIND(" ",T26),"")

because T26 is text rather than a numeric date+time.

[hrlngrv]

. . . However, the T26 cells format is 'd h:mm'. . . .

Irrelevant. The actual entry in cell T26 is text. Try =COUNT(T26). If it returns 0, then T26 isn't a numeric date+time.

I don't believe Excel will accept date+time entries like 17 2:33. I tried, admittedly using Excel 2000, first formatting a cell with number format d h:mm. I entered 17 2:33, and Excel entered it as text.