Calculation of days between days and time

[theprincesunil]

Hi

I need a formula for calculating days and time. I have attached excel for example how it could be.

Thanks
Sunil

[Special-K]

For total minutes

=(B2-A2)*1440

Make sure all dates are the same format (yours are not) and that they are proper date/times (not text)

[Ace_XL]

In C2
=$B2-$A2
Custom Format : d" Days"

In D2
=$B2-$A2
Custom Format : [mm] "Mins"

In E2
=TEXT(C2,"d")&" days "&TEXT(D2,"[mm]")&" Mins"

Copy down

[newdoverman]

I think that this will do what you want.

[theprincesunil]

hi Dover,

If i click on f2 button for seeing formula in cell. it is comeing #value error.

When i am trying rebuild the same formula in different cell also it is coming as #value error.

Thanks
Sunil

[theprincesunil]

Hi Ace,

Sorry to say u, I am not able to solve with that formula.

Thanks
Sunil

[Special-K]

I notice your date cells have TWO spaces between the date and time, maybe these being picked up as text values, they should be numeric.

Select each date, hit F2 then enter, the date/time appears to reformat to a number and the calculation then works.

[theprincesunil]

Hi Special,

No luck for me. i removed. still it is showing same error

Regards
Sunil

[theprincesunil]

Hi Special,

No luck for me. i removed. still it is showing same error

Regards
Sunil

[Ace_XL]

See attached. Row 5 has '0' input value leading to erroneous output

[theprincesunil]

Hi Ace,

Thanks a lot for your help. Just need one more help on result cell. I need it like 1 hour 20 mint. i tried but it is giving incorrect mints.

Thanks
Sunil

[Ace_XL]

In E2

=TEXT(C2,"d")&" days "&TEXT(D2,"[HH]")&" Hrs "&MOD(TEXT(D2,"[mm]"),60)&"mnts"

[theprincesunil]

It works super. Thanks a lot ACE.

[newdoverman]

I can't replicate your problem. I hit F2 and the formula appears properly....no errors at all. I just works perfectly.