Getting a formula to Evaluate Time Properly

**tehmole** · 08-22-2008, 11:07 AM

Good morning everyone,

i ran into a bottle of confusion this morning - hope some one can help -

this is my formula -

=INT(Q27/7*24)&" days, "&TEXT(MOD(Q27,7/24),"h"" hrs ""m"" mins""")

Q27 = 14:00

Format: [hh]:mm

evaluates: #VALUE!

i tried changing the formats, entries.....

If Q27 = 14:04

evaluates: 2 days, 0 hrs 1 mins
----------------------------------------------

can someone please help, i'm pulling my hair out....thank you ssssoo much in advance...

**NBVC** · 08-22-2008, 11:09 AM

Hi,

Please read our forum rules and post a title that is more descriptive of your question.

**NBVC** · 08-22-2008, 11:46 AM

With that formula, you have to enter 14:00 literally that way..not just 14... Then you will get a result.

**tehmole** · 08-22-2008, 12:04 PM

i do enter 14:00 -

if you plot in 21:00 it'll display #VALUE!

but then if you plot 20:35 it will display 2 days, 6hrs 35 mins

**NBVC** · 08-22-2008, 12:32 PM

Your Mod() function is returning a negative...and you can't have negative time.

Try inserting the ABS() function:

=INT(Q27/7*24)&" days, "&TEXT(ABS(MOD(Q27,7/24)),"h"" hrs ""m"" mins""")

**tehmole** · 08-22-2008, 01:00 PM

thank you so much NBVC

worked like a charm..........ty ty ty

**daddylonglegs** · 08-22-2008, 01:21 PM

Originally Posted by NBVC

Your Mod() function is returning a negative

Interesting. Because 7/24 is positive the result of MOD(x,7/24) can't ever be negative.......in theory

**NBVC** · 08-22-2008, 01:32 PM

I noticed that too...

It seems when the result of the Mod(Q27,7/24) should be 0, it gives the negative result....

maybe it has to do with the precision of 7/24?