With DATEDIF() it's possible to calculate a date difference for two dates.
However, in the new online product Excel Online (Office 365) this function seems to be not available.
How comes and how to calculate this?
Answer
cybernetic.nomad is correct in that Microsoft says it's supposed to be there. But it looks like Microsoft lied. :-) I just checked Excel Online. You're right, it isn't there.
You can do a simple date subtraction, but there are a few things to be aware of.
DATEDIF ensures that you're working with dates rather than date/times. Dates and times are stored together (dates are day counts, stored as the integer portion of the value; times are fraction of a day, stored with the day count as a decimal fraction). A pure date is stored as a date as of crossing midnight (time of 0).
If your values are date/times, simply subtracting the values will give a result that includes a time difference, which could round to another day and give you the wrong result. Even without rounding, the result can still include a fraction of a day, which can give you inaccurate results in calculations if the calculations are based on whole day counts. In that case, you can use the integer function to chop off times before subtraction (
=INT(B1)-INT(A1)).DATEDIF also adds a feature over a simple subtraction: you can specify the units you want, and it can do some convenient conversion functions. Subtraction will give you the difference in days (those are the units that dates are stored in). So if you want other units, you would need to convert the result to the units you want.
And yes, you can use TODAY as one of the dates, but recognize that it's volatile (it gets updated every time anything in the spreadsheet changes). So tomorrow, your spreadsheet will show a different date difference. If you're working with hard-coded dates, the result won't change. Of course, if you are doing something like tracking days remaining or elapsed days, you might want to use TODAY for a dynamic value.
No comments:
Post a Comment