Skip to content
ProjectControls.com
Microsoft Project

Alter of Excel cell

In the Open Standard
Hady Shendy, PMPMember for 20 years 4 months
Created
3
4
0
Hi All
in excel if I need to custom a cell as a duration value
like if I have a cell for "Period for recovery of investment money" value = 11.16 year
how I convert this figure to year:month:day (i.e. 11y:2m:15d)
Save Follow Follow forum Report
Reply New topic

3 replies

Oldest first
  1. 0
    Svein Myklebust ·
    #1
    Why do you need this?

    Excels calcualtions on time ar based on days.
    Why can’t you just use days?

    Svein
    Report
  2. 0
    Zhang Haixiang ·
    #2
    you need some formula,
    cell A1 = 11.6 then

    year: int(a1)
    month: int(mod(a1*365,365)/30)
    day:: int(mod(a1*365,30))

    you can input the following formula in Cell B1

    =int(a1)&"y:"&int(mod(a1*365,365)/30)&"m:"&int(mod(a1*365,30))&"d"

    But the result is not 100% acurate, as the caculation is based on 365days/year and 30days/month. Only when you have the start date and finish date then you can get the acurate result
    Report
  3. 0
    Hady Shendy, PMP ·
    #3
    Many Thanks zhang
    keep in tuch
    Report

Log in or join to reply

ProjectControls.com is free to join. Members can post questions, reply, vote and follow discussions.

Log inJoin free

Existing members: your original username and password still work. Forgot password?