Sunset Calculator help

[b_rianv]

Good evening guys,

I am trying my best here with my formulas and have really come far with you guys teaching me. Thanks for all the help with my Ocean tides calculator to everyone that helped me.
Now I am working on my last little project for my Ocean tides Calculator and I am doing this one Sunset calculator. I am trying to use formulas like this

=INT(AA2/60)&":"&IF(LEN(MOD(AA2,60))=1,"0"&MOD(AA2,60),MOD(AA2,60))...Should I use this for time to minutes

=DATEDIF(B1,A1,"d")

=IF(A2="","",IF(ISNA(A2),NA(),A2-TODAY()&"DAYS remaining"))

=NOW()

=CONVERT(A2,"mn","hr")

But I am getting lost with what excel is telling me, looks like it is trying to work but I am not seeing what I feel is right.
Please look at my spreadsheet.

Brian

[MrShorty]

It looks correct to me, for the formula you are giving it. As explained in the link suggested previously (http://www.cpearson.com/Excel/datetime.htm ), Excel stores dates and times as days and fractions of days. 11 August 2016 at noon is actually the value of 42593.5 (this is what NOW() would return). TODAY() returns the date without the time information (42593). So NOW()-TODAY() would be 0.5. The date time formats that we like to see are determined by number formatting (see this for a list of number format codes, including those that control date and time formats https://support.office.com/en-us/art...2-09fab54be7f4 ).

Where you want to include a date and time value in with another text string, you will need to use the TEXT() function to control how that date/time serial number (0.5 in example) is displayed in the text string. https://support.office.com/en-us/art...8-93d29371225c

=NOW()-TODAY()&"text" will return 0.5text
=TEXT(0.5,"[hh]:mm")&"text" will return 12:00text

[b_rianv]

Thank you MrShorty for your help it took me to the right direction.