Excel formula to calculate electricity bill

[JohnW2]

"The status of DATEDIF in Excel is somewhat mysterious. DATEDIF (Date + Dif) is a "compatibility" function that comes from Lotus 1-2-3 way back in the 1990s. Although it's available in all Excel versions since that time, it will not autocomplete in the formula bar, and Excel will not help you fill in arguments for DATEDIF like other functions. In the immortal words of the late, great Chip Pearson: DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Yet DATEDIF remains an important function for problems that involve calculating the time between two dates."

In most programming languages in which (as is usually the case) dates are stored as a number of days since some specified starting point (commonly 1st January 1960, sometimes 1900 - and maybe one day 2000 ) (and I think that includes Excel and VBA) one doesn't really need a 'date difference' function (although there often is one, since subtracting the value of one date variable from another will give the difference in days, which one can then convert (or format) into some other 'interval units' if one wishes.

 

[Diver Fred]

In most programming languages in which (as is usually the case) dates are stored as a number of days since some specified starting point (commonly 1st January 1960, sometimes 1900 - and maybe one day 2000 ) (and I think that includes Excel and VBA) one doesn't really need a 'date difference' function (although there often is one, since subtracting the value of one date variable from another will give the difference in days, which one can then convert (or format) into some other 'interval units' if one wishes.

Showing results for Excel epoch date

Excel supports two date systems, the 1900 date system and the 1904 date system. Each date system uses a unique starting date from which all other workbook dates are calculated. All newer versions of Excel calculate dates based on the 1900 date system, while older versions used the 1904 system.

 

[JohnW2]

Showing results for Excel epoch date

Excel supports two date systems, the 1900 date system and the 1904 date system. Each date system uses a unique starting date from which all other workbook dates are calculated. All newer versions of Excel calculate dates based on the 1900 date system, while older versions used the 1904 system.

As I said, "sometimes" 1st January 1900

It doesn't really matter what start date' is chosen, since the form in which the date is stored is not normally 'visible' to the user and I think all languages allow the stored date to be negative (i.e. for dates before the chosen 'start date', so can cope with dates as far back into the past as one wants (give or take the confusions created by long-ago historical changes in the calendar system!).

 

[fixitflav]

The DATEDIF function has been in Excel for a very long time, maybe even 'for ever' (i.e. since Excel was first born!). The oldest version I have to hand is Excel 2000 (undoubtedly written more than 25 years ago), and it's certainly in that.

Interesting. I have an old XP computer and a W11, there are 14 to choose from in Date & Time on W11, but 20 on the XP! And DATEDIF isn't on either of them. Though as I said, it does recognise it when you type it in.

The "third" argument it is asking for is a specification of the desired units of the value it returns, the most straightforward being "D", "M" and "Y" for days, months and years respectively.

Tried all them and it doesn't work for me! Just curious, there's an easier way of doing it anyway (for days at least). If using it for months or years it looks like you'd have to be careful XL's interpretatiion of whole periods agreed with what you're trying to do.

 

[JohnW2]

Interesting. I have an old XP computer and a W11, there are 14 to choose from in Date & Time on W11, but 20 on the XP! And DATEDIF isn't on either of them. Though as I said, it does recognise it when you type it in.

From the ('integrated', not on-line) 'Help' of Excel 2000 (Excel 9.0.2720) ....

 

[JohnW2]

Tried all them and it doesn't work for me! Just curious, there's an easier way of doing it anyway (for days at least). If using it for months or years it looks like you'd have to be careful XL's interpretatiion of whole periods agreed with what you're trying to do.

With =DATEDIF(B4,C4,"D") in cell E4 and =C4-B4 in cell G4 (and G4 formatted as 'number' with zero decimal places) both work for me in Excel 2000, Excel 2010 and Excel 2019 (illustration is of Excel 2019, but the other two look identical). What's in G4 is the ';easier method' I mentioned previously.

 

[fixitflav]

With =DATEDIF(B4,C4,"D") in cell E4 and =C4-B4 in cell G4 (and G4 formatted as 'number' with zero decimal places) both work for me in Excel 200, Excel 2010 and Excel 2019 (illustration is of Excel 2019, but the other two look identical). What's in G4 is the ';easier method' I mentioned previously.

OK thanks. I think the reason it doesn't work for me is it needs an Add-in to XL. To do that it wants to know the XL installation source, which I don't have, it came with the computer! Not that it matters, just trying it out of curiosity.

One last question - on yours, is DATEDIF on the functions list, or do you type it in?

 

[JohnW2]

OK thanks. I think the reason it doesn't work for me is it needs an Add-in to XL. To do that it wants to know the XL installation source, which I don't have, it came with the computer! Not that it matters, just trying it out of curiosity.

Maybe. In all three versions of ExcelI tried (2000, 2010 & 2019), I have only one "Add-In" installed, namely "Analysis ToolPak"

However, as I've said and illustrated, if you just want the difference between two dates, you can just get that 'by subtraction' without needing any function.

One last question - on yours, is DATEDIF on the functions list, or do you type it in?

No, you're right, it's not in the list (see screenshot from Excel 2000 below). I was not aware of that because I don't think that I have ever in my life used that list - I've always just 'typed things in'

Kind Regards, John

 

[fixitflav]

No, you're right, it's not in the list (see screenshot from Excel 2000 below). I was not have been aware of that because I don't think that I have ever in my life used that list - I've always just 'typed things in'

Thanks

 

[JohnW2]

Thanks

You're welcome

 

[fixitflav]

You're welcome

Just a couple of things. I've noticed there is a DATEDIF function listed on Google sheets. And DATEDIF does work om my XL, where I was going wrong was not putting the 3rd argument in quotes, "d" etc.

While I'm on, I wonder if others also find irritating the habit of energy companies to give estimated readings to the nearest 0.1 unit. As actual readings are whole numbers this is pointless. No doubt the figure is generated by an algorithm but it could easily be set up to round to a whole number.

 

[@tlan]

Hence, if A2 were 2, A3 were 3 and A4 were 4, then
=A2+A3*A4
... would give an answer of 24

No it wouldn't, 14 more like.

 

[JohnW2]

No it wouldn't, 14 more like.

Good grief - has it taken you two years (and four days !) to notice that typo of mine?

As you say, the answer that Excel (or most programming languages) would give for 2+3*14 is 14, for the reason I explained in that ancient post you've just found - my typing fingers simply typed "24", rather than the correct "14". !!!

 

[@tlan]

Nope. Post was revived at 2.13pm today . Live with it.

 

[JohnW2]

Nope. Post was revived at 2.13pm today . Live with it.

The thread was continued at 2:13pm today, having been 'revived' about a week ago (on 16 May 2024), but the post of mine in which you have just diligently detected a typo was posted on 20 May 2022, just over 4 years ago