Counting from & to specific dates & times on a spreadsheet - mostly done, but some bugs

Question

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!

Answer 1

:Caveat:

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

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.