Nested If Formula with DATE / TIME / INT & MOD: Simpler “1 CELL” Expression!?

[Stephan]

Hello

SIMPLER "1 CELL" EXPRESSION then this? It is CALC of: FINISH DATE/TIME - START = DECIMAL HOURS

=IF(DATE(YEAR(C25),MONTH(C25),DAY(C25))=DATE(YEAR(F25),MONTH(F25),DAY(F25)),(F25-C25)*24,IF(F25-C25>=TIME(23,59,59),INT((F25-C25)*24),MOD(F25-C25,1)*24))

This is…. an NESTED If FORMULA with DATE / TIME / INT & MOD, I’ve amalgamated (Joined..) together from separate Formulas to workaround the 24H HOUR MAX limitations of XL Dates/Times as conventional TIME (HH:MM) is Ltd to same day in same 24hr. To Further explain this in 3 Parts, Formula in plain English 1st of all:

SAME DAY: SUBTRACT (TIME CELLS no DATE)
If DIFFERENT DAY >24+: INT (DATE CELLS with TIME)
If DIFFERENT DAY <24-: MOD (DATE CELLS with TIME)

And in more detail, with Formula Passage ref’d of 1 row as E.G., to indicate its purpose.

1. DATE CALC: SAME DAY or DIFFERENT DAY?
=IF(DATE(YEAR(C25),MONTH(C25),DAY(C25))=DATE(YEAR(F25),MONTH(F25),DAY(F25))

2. DATE CALC: SAME DAY
(F25-C25)*24

3. DATE CALC: IF DIFFERENT DAY, LESS or GREATER then 24HRS?
IF(F25-C25>=TIME(23,59,59)

4. DATE CALC: DIFFERENT DAY >24HR
INT((F25-C25)*24)

5. DATE CALC: DIFFERENT DAY <24HR
MOD(F25-C25,1)*24)

Please see, also attached sheet:

COLUMN: M
ROWS: 25 - 30

Purpose is to calculate the cost of GAS PER HOUR AVERAGE.

It is derived from used GAS M3 > GAS KWH hence GAS CONSUMED PER USAGE with DATE/TIME START/FINISH, which is converted into DECIMAL NUMBER with the TITLE TOP FORMULA for ease of calculation of:

GAS KWH USED = £ PER HOUR AVERAGE (ie: G = £)

COLUMN: K
ROWS: 25 - 30

Anyways.. This all works by the way, with.. current Data Set, obviously if not recorded at exact HOUR to HOUR in WHOLE NUMBER then requires.. this complicated Formula, due to.. DECIMAL PLACES… which is where MOD (<24HR pre-fer in 1 HOUR BLOCKS) Formula is required! The INT (>24HR+) Formula is needed for accurate calculation exceeding the 24 Hour threshold of Excel Time…

I’m not desperate for other remedys or long winded remedial processes, as it does work, all hard work done, just choose correct DATEs to COPY & PASTE, enter READINGS etc. And yes it is already is realistically a 1 CELL FORMULA wrap, I know that, took some thought as not expressions I ever used regular with EXCEL spreadsheets, having said that is there a better way,, I don’t know, Suits me so far… So constructive advise I welcome…!

But any thoughts to why Excel has made DATES with TIMES such an over complication, EG: Why not just let DATE with TIME be accurate! DD MM YY HH MM SS

Cheers Stephan


CROSSTHREADS: keen on these forums, here is few bolts to that link n others...
https://www.excelforum.com/excel-fo...od-simpler-1-cell-expression.html#post6042244

DATE / TIME / INT / MOD / NESTED IF FORMULA: SIMPLER “1 CELL...

Hello SIMPLER EXPRESSION “1 CELL” then this? It is CALC of: FINISH DATE/TIME - START = DECIMAL HOURS =IF(DATE(YEAR(C25),MONTH(C25),DAY(C25))=DA...

www.myonlinetraininghub.com

 

[Stephan]

Hello

What's the Difference between 2 Dates! ?

Solved Solution is:
=CONVERT(F25-C25,"day","hr")
It looks so simple and gladly it works as is, but wouldn't have reached that conclusion from formula definition, and hence Google search would have been futile.

Shouldn't have been this difficult should it! Really Modern Excel should do this in the very 1st place when using Subtract (negative -) with Dates..:
After - Before = HOURS

As much fun as it was Constructing the Nested If, due to Dates calc that weren't Whole numbers which were incorrectly stated/reported, and obviously Same Day Hours max is 24 Hours... But Subtract works with that accurately!
HOURS that is...

Cheers anyways... Stephan

 

[p45cal]

=(F25-C25)*24
?
Ensure Excel doesn't automatically convert the cell format to time or date after you commit the formula to the sheet (make sure it stays as a plain number or General).

 

[p45cal]

…now where in Hull?

 

[Stephan]

…now where in Hull?

Anlaby Village near Santi's (Red Lion), near to Beach Tree Southella..

Spoken before, re-going to Cott Rd @ Humberside University in 1990s

In the Days of popular Bev Rd with Students, Scruffy Murphys... Gardners Arms... Circus Cirucus... LAs...

 

[Stephan]

=(F25-C25)*24
?
Ensure Excel doesn't automatically convert the cell format to time or date after you commit the formula to the sheet (make sure it stays as a plain number or General).

Hi, FORMAT CELLS: NUMBER is retained.
Calc of: GAS KWH / HOURS DECIMAL.
But you're right if was in FORMAT CELLS: HOURS then Calc is incorrect.

so if 2 DIFFERENT DATES (FINISH - START) = HOURS ACTUAL (NOT DECIMAL)

What is correct FORMULA & FORMAT CELLS? Custom?
Ain't it odd basic SUBTRACT (-) of AFTER-BEFORE doesn't equal right HOURS!

SOLUTION!

=CONVERT(F29-C29,"day","hr")/24

FORMAT CELLS: [HH]:MM

 

[p45cal]

Ain't it odd basic SUBTRACT (-) of AFTER-BEFORE doesn't equal right HOURS!

No, but it does equal right days (and fractions of days).

All dates and times in Excel are in units of days. 12 hours (half a day) is 0.5 (put 0.5 in a cell and format at it as [HH]:MM and you'll see 12:00. The underlying value will still be 0.5).
CONVERT(F29-C29,"day","hr") only multiplies (F29-C29) by 24. If you then divide that result by 24 you'll get days back. So you don't need to convert then divide by 24; the same result is obtained by:
=F29-C29
and format it [HH]:MM
Formatting a cell only changes how a cell looks, it doesn't change the underlying value. Excel calculates using underlying values.

To get your calculations right (the units are KWH where H means Hours), you have to have the underlying value in hours, so you use (F29-C29) which gives you the time difference in days, and multiply by 24.

re:

Spoken before

Oops, sorry, had forgotten it was you!

 

[Stephan]

Hey thanks, you've probably just woke up! & Hull, not much of a wonder is it.. Odd that Town night life these days is just centered around Low Gate & ye old WhiteFriarGate.. Still u won't hear Oasis whining out of Rio's...

Also BRILLIANT plain English definition in some terms I can see.

Such as my perception of Time/Date it cud be presented in... Days.. Hours whatever.. So defined limitations won't occur to me imminently until rake through it... with existing Data Set... Yes Yes that's Right... etc..

so also other then a recent realisation some calc in XL for Time are Ltd to 24hrs. Time it all same to me, but appreciate time between dates are exact.. Well Format Cells Date to Hours, I'll perceive as same..

I did read the MS Definition of CONVERT, but this was more like an intellectual cover up with vague bureautic waffle! It's actual mathematical uses were alot less obvious, then your explanation! I appreciate all that..

Also for such a concise FORMULA it's simplicity is fine, and for consistency now I know what I'm really looking at.

Tbh I was anticipating some odd Long winded suggestions!

My experience with XL is generally statistics, occasionally TIMES same day which are simple basic "SUBTRACT / NEGATIVE / -".

However rarely DATES, other then some overly long calc of HOW MANY WEEKS is that..

Which Conjoured up thoughts of DATE DIFFERENCE, and remembering this an area of boring onerousity fraught with likely/possible human error!

Hence this.... HOURS (AFTER DATE - BEFORE DATE)... is Just what I need...

=CONVERT(F29-C29,"day","hr")

It is far simpler then I hoped for, as more times (ha ha), turn a basic task into some sort of virtually impossible guessing game!

 

[Stephan]

No, but it does equal right days (and fractions of days).

All dates and times in Excel are in units of days. 12 hours (half a day) is 0.5 (put 0.5 in a cell and format at it as [HH]:MM and you'll see 12:00. The underlying value will still be 0.5).
CONVERT(F29-C29,"day","hr") only multiplies (F29-C29) by 24. If you then divide that result by 24 you'll get days back. So you don't need to convert then divide by 24; the same result is obtained by:
=F29-C29
and format it [HH]:MM
Formatting a cell only changes how a cell looks, it doesn't change the underlying value. Excel calculates using underlying values.

To get your calculations right (the units are KWH where H means Hours), you have to have the underlying value in hours, so you use (F29-C29) which gives you the time difference in days, and multiply by 24.

re:

Oops, sorry, had forgotten it was you!

Hello

HOWEVER what I did notice... &. this maybe Hard to answer without Older Version YEAR.. That is Who: EXCEL 2003. Probably what is the Formula without newer Formula / Functions.. Really... Long Hand.. Whatever that is/was...

In other Words (without BS) :

OLDER VERSIONS of EXCEL doesn't recognise CONVERT..

so for EXCEL 2003 what is the correct FORMULA & FORMAT CELLS?

HOURS = END DATE - START DATE

DATE1:
01/01/2025 00:00

DATE2:
02/01/2025 00:00

Just coz Older Versions OF Excel dunno what CONVERT is...

Luckily.. for the excellent version EXCEL 2021, I Prefer CONVERT for uniformity of Copy/Paste, instead of:

What do I need for 2 Different Dates, in either HOURS or DECIMALS, again...
(not a question, just a rhetorical statement)

 

[Stephan]

Further to above IT appears (-) SUBTRACT (TAKE-AWAY) does actually work in
FORMAT CELLS: CUSTOM DATE+HOURS of dd/mm/yyyy hh:mm
but only when the
end sum total is FORMATTED ...
FINISH or NEW DATE (SUBTRACT) START or OLD DATE is FORMAT CELLS: [HH]:MM !

But in OLDER VERSION... EXCEL 2003, unsure what is CORRECT CALC to DECIMAL PLACES,
in 1 CELL CONCISE....

Did see a really long way to Present it, if you fancy losing the will to live... for different columns per HH / MM / SS but only for each, that no use..

Again why in DECIMALS, just easier to check if that right...

 

[Stephan]

WORKED IT OUT... in the OLDER VERSION of EXCEL 2003, to CALC DECIMAL HOURS from DATE TAKEWAY...

Yes calc sum does need * (MULTIPLYING BY 24), but found it needed a little tweaking...
only worked DIRECT REF to another CELL, or 1CELL SAME as this, I have no idea why it needed to be in Brackets though, absolutely clueless like..:

=SUM(L153-K153)*24

FORMAT CELLS: NUMBER

 

[p45cal]

re:
=SUM(L153-K153)*24

The SUM part is superflous, all you need is
=(L153-K153)*24
which is what I proposed in msg #3.

I have no idea why it needed to be in Brackets though, absolutely clueless like.

It's to do with something called 'operator precedence' (internet search it); the order in which calculations are done. Multiplications are done first then subtractions.
Without the brackets it would be K153*24, then subtract that from L153 and that would give you the wrong result.
By putting the subtraction in brackets the subtraction is done first, then that result multiplied by 24.

 

[Stephan]

re:
=SUM(L153-K153)*24

The SUM part is superflous, all you need is
=(L153-K153)*24
which is what I proposed in msg #3.

It's to do with something called 'operator precedence' (internet search it); the order in which calculations are done. Multiplications are done first then subtractions.
Without the brackets it would be K153*24, then subtract that from L153 and that would give you the wrong result.
By putting the subtraction in brackets the subtraction is done first, then that result multiplied by 24.

Right... I know every1 sayes, oooh Decimals.. yer.. Just times 24 it... & it never worked, guess they left that CRUCIAL bit of advice out! Sum Needs BRACKETS..

also alot INTERNET SITES for XL formula calc for CALC of HOURS, alot of these leave out, those Calcs are LTD to 24hr, and not UNLIMITED, or that HH needs BRACKETS...

I know why..not need to work these out before..Dates are Dates, it is what is, like work shift, total was HRs, or on this Day Cat got out etc...

Which is why the obviousness of this has Evaded me, glad to know this calc can be made in a SIMPLY/BASIC way that even works in the OLDER VERSION EXCEL 2003, and wasn't that a good Program... Yes it was! XL 2021 has excellent time saving features though!

E.G:
FORMULA in SIMPLE/BASIC all XL versions:

HOURS = END DATE2 - START DATE1

DATE1: Cell A1
01/01/2025 00:00
FORMAT CELLS: CUSTOM dd/mm/yyyy hh:mm

DATE2: Cell A2
02/01/2025 00:00
FORMAT CELLS: CUSTOM dd/mm/yyyy hh:mm
HOURS FORMULA: Cell A3
A2-A1
FORMAT CELLS: CUSTOM [HH]:MM

or

HOURS DECIMAL: Cell A4
=SUM(A2-A1)*24
FORMAT CELLS: NUMBER

Cheers Stephan

 

[Stephan]

re:
=SUM(L153-K153)*24

The SUM part is superflous, all you need is
=(L153-K153)*24
which is what I proposed in msg #3.

It's to do with something called 'operator precedence' (internet search it); the order in which calculations are done. Multiplications are done first then subtractions.
Without the brackets it would be K153*24, then subtract that from L153 and that would give you the wrong result.
By putting the subtraction in brackets the subtraction is done first, then that result multiplied by 24.

Hello

Any thoughts when not just HOURS, say in absolutely...:
YEARS - MTH - DD - HH:MM?

Best concise 1CELL version with minimal repetitive Refs to SubTract (End-Start) so far

=CONCAT(DATEDIF(A1,B1,{"y","ym","md"})&{" Year "," Mth "," Day "})&TEXT(B1-A1,"h\Hr m\M")

Doubt any1 can better that?

Subtract B1-A1 with FORMAT CELLS CUSTOM: yy "Yr" m "Mth" d "Day" hh:mm
But DATE is in ERROR.

Full Post here:
https://chandoo.org/forum/threads/d...ead-of-concertina-string-together-mess.61455/