1

I wanted to make a spreadsheet that I could enter a date & time and calculate how much time (in years/dates/months/weeks/hours/minutes/seconds...)

I kept expanding on it. it serves very little functional purpose but it was fun... here is the file...

https://docs.google.com/spreadsheets/d/19WJ7ld-SN6kWfCec94NDu5yEk8Md04j9i_O7C4KtkYw/edit?usp=sharing

Some things I need help with

  • my month calculator doesn't seem to work... for the future? (red on sample)
  • my time to/from calculator seems to add 12 hours? i'm guessing I need to do it a different way... column f
  • for calculators on specific days of every year (e.g. enter a birthday, and if you open the file in 2020 or 2024 i want it to show days to/from that same year...)

thoughts?

Thanks!

0

:Caveat:

Show method is based on data, shared through Google sheet.

enter image description here

How it works:

  • Formula in Cell M2:

    =DATEDIF(L2,NOW(),"y") &" years, "&DATEDIF(L2,NOW(),"ym") &" months, " &DATEDIF(L2,NOW(),"md") &" days"
    
  • Formula in Cell N2:

    =INT((NOW()-L2)*24) &" Hrs, " & INT((NOW()-L2)*24*60)&" Min, " & INT((NOW()-L2)*24*60*60)& " Seco"
    
  • Formula in Cell O2:

    =DATEDIF(L2,NOW(),"Y")

  • Formula in Cell P2:

    =DATEDIF(L2,NOW(),"m")

  • Formula in Cell Q2:

    =CEILING.MATH(DATEDIF(L2,NOW(),"D")/7,1)

  • Formula in Cell R2:

    =DATEDIF(L2,NOW(),"d")

  • Formula in Cell S2:

    =INT((NOW()-L2)*24)

  • Formula in Cell T2:

    =INT((NOW()-L2)*24*60)

  • Formula in Cell U2:

    =INT((NOW()-L2)*24*60*60)

N.B.

  • Cell L3, has Bith date & Time of Dr. Martin Luther king Jr.
  • You may adjust cell references in the formula as needed.
2
  • I appreciate the effort, those formulas don't seem to work better or address the issue in mine, but interesting :) thanks for trying! – Korgun Jun 19 '20 at 3:34
  • @Korgun,,, no issue,, I've tried almost what has been solved in attached google sheet,,, if possible just write which one is different ! – Rajesh S Jun 19 '20 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.