Rocksolid Light

Welcome to RetroBBS

mail  files  register  newsreader  groups  login

Message-ID:  

"Can you program?" "Well, I'm literate, if that's what you mean!"

computers / alt.comp.os.windows-10 / Re: Excel time addition formula isn't working

SubjectAuthor
* Excel time addition formula isn't workingandrew
+- Re: Excel time addition formula isn't workingAndy Burns
+- Re: Excel time addition formula isn't workingChris
`- Re: Excel time addition formula isn't workingPhilip Herlihy

1
Excel time addition formula isn't working

<tgi721$2arjf$1@news.mixmin.net>

  copy mid

https://www.rocksolidbbs.com/computers/article-flat.php?id=68601&group=alt.comp.os.windows-10#68601

  copy link   Newsgroups: alt.comp.os.windows-10
Path: i2pn2.org!i2pn.org!aioe.org!news.mixmin.net!.POSTED!not-for-mail
From: andrew@nospammingme.com (andrew)
Newsgroups: alt.comp.os.windows-10
Subject: Excel time addition formula isn't working
Date: Fri, 23 Sep 2022 03:16:40 +0930
Organization: Mixmin
Message-ID: <tgi721$2arjf$1@news.mixmin.net>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Injection-Date: Thu, 22 Sep 2022 17:46:41 -0000 (UTC)
Injection-Info: news.mixmin.net; posting-host="fb8e096ea7fccfac4acbbfcd170d1e8ad5ff4bcf";
logging-data="2453103"; mail-complaints-to="abuse@mixmin.net"
 by: andrew - Thu, 22 Sep 2022 17:46 UTC

I'm summing up time.

Each column has a header of "Date", "Start" "End" & "Daily" time.
Column A is the date (formatted as "Date").
The next two columns are start & end times (formatted as "Time" "1:30 PM").
Column D is the total for each day (formatted as "Time" "1:30").
That's working.

The total for all of them isn't working.
That cell has the formula "=SUM(D2:D16)" but it's coming up too low.
That cell (E2) is formatted as "Time" "1:30").

This is so simple a task I can't believe the formula isn't working.
Any suggestions?

Re: Excel time addition formula isn't working

<jp3nheF43hpU1@mid.individual.net>

  copy mid

https://www.rocksolidbbs.com/computers/article-flat.php?id=68604&group=alt.comp.os.windows-10#68604

  copy link   Newsgroups: alt.comp.os.windows-10
Path: i2pn2.org!i2pn.org!news.uzoreto.com!newsreader4.netcologne.de!news.netcologne.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: usenet@andyburns.uk (Andy Burns)
Newsgroups: alt.comp.os.windows-10
Subject: Re: Excel time addition formula isn't working
Date: Thu, 22 Sep 2022 19:23:41 +0100
Lines: 22
Message-ID: <jp3nheF43hpU1@mid.individual.net>
References: <tgi721$2arjf$1@news.mixmin.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net RBa/YBpn0YuIPgWXAB24DwwUvkqGWF3ckcLGA3O/HOUof6gB1u
Cancel-Lock: sha1:efZmoVP+jAuPHvQ+XlyvRpEjiD8=
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
Thunderbird/102.3.0
Content-Language: en-GB
In-Reply-To: <tgi721$2arjf$1@news.mixmin.net>
 by: Andy Burns - Thu, 22 Sep 2022 18:23 UTC

andrew wrote:

> I'm summing up time.
>
> Each column has a header of "Date", "Start" "End" & "Daily" time.
> Column A is the date (formatted as "Date").
> The next two columns are start & end times (formatted as "Time" "1:30 PM").
> Column D is the total for each day (formatted as "Time" "1:30").
> That's working.
>
> The total for all of them isn't working.
> That cell has the formula "=SUM(D2:D16)" but it's coming up too low.
> That cell (E2) is formatted as "Time" "1:30").
>
> This is so simple a task I can't believe the formula isn't working.
> Any suggestions?

Th answer is probably here

<https://www.ablebits.com/office-addins-blog/2015/06/24/calculate-time-excel/>

likely you need a different format if the sum exceeds a day

Re: Excel time addition formula isn't working

<tgid7a$29noc$1@dont-email.me>

  copy mid

https://www.rocksolidbbs.com/computers/article-flat.php?id=68606&group=alt.comp.os.windows-10#68606

  copy link   Newsgroups: alt.comp.os.windows-10
Path: i2pn2.org!i2pn.org!eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From: ithinkiam@gmail.com (Chris)
Newsgroups: alt.comp.os.windows-10
Subject: Re: Excel time addition formula isn't working
Date: Thu, 22 Sep 2022 19:31:54 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 25
Message-ID: <tgid7a$29noc$1@dont-email.me>
References: <tgi721$2arjf$1@news.mixmin.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Injection-Date: Thu, 22 Sep 2022 19:31:54 -0000 (UTC)
Injection-Info: reader01.eternal-september.org; posting-host="e6482e09b71db51a3ccf98f81d6fc623";
logging-data="2416396"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/d7i92XPcLKF9tpmw8YZOHJvS5z9A2HHs="
User-Agent: NewsTap/5.5 (iPhone/iPod Touch)
Cancel-Lock: sha1:0Uemyzx/yAAYWt8cdM+dBTmEsag=
sha1:6nKVzvKHIXfxI15NJmJQ1SP9u7Y=
 by: Chris - Thu, 22 Sep 2022 19:31 UTC

andrew <andrew@nospammingme.com> wrote:
> I'm summing up time.
>
> Each column has a header of "Date", "Start" "End" & "Daily" time.
> Column A is the date (formatted as "Date").
> The next two columns are start & end times (formatted as "Time" "1:30 PM").
> Column D is the total for each day (formatted as "Time" "1:30").
> That's working.

You don't want column D formatted as "Time" you want to have it as a number
either in minutes or hours.

> The total for all of them isn't working.
> That cell has the formula "=SUM(D2:D16)" but it's coming up too low.
> That cell (E2) is formatted as "Time" "1:30").

That's because it's summing the times of the day, can't go over 12:00 or
23:59 so overflows.

> This is so simple a task I can't believe the formula isn't working.
> Any suggestions?

It's working, just not how want it to.

Re: Excel time addition formula isn't working

<MPG.3d96e560a143c22f989a1d@news.eternal-september.org>

  copy mid

https://www.rocksolidbbs.com/computers/article-flat.php?id=68607&group=alt.comp.os.windows-10#68607

  copy link   Newsgroups: alt.comp.os.windows-10
Path: i2pn2.org!i2pn.org!paganini.bofh.team!eternal-september.org!reader01.eternal-september.org!.POSTED!not-for-mail
From: PhillipHerlihy@SlashDevNull.invalid (Philip Herlihy)
Newsgroups: alt.comp.os.windows-10
Subject: Re: Excel time addition formula isn't working
Date: Thu, 22 Sep 2022 22:34:58 +0100
Organization: A noiseless patient Spider
Lines: 41
Message-ID: <MPG.3d96e560a143c22f989a1d@news.eternal-september.org>
References: <tgi721$2arjf$1@news.mixmin.net>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Injection-Info: reader01.eternal-september.org; posting-host="292f0280456cddc64138e79ac0f3dc86";
logging-data="2430518"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX192xfmOoRV3+BB9wyv5GJknMOoyV4wKt6k="
User-Agent: MicroPlanet-Gravity/3.0.10 (GRC)
Cancel-Lock: sha1:d3BukArmKYH+zkbMbyesfwCBIiE=
 by: Philip Herlihy - Thu, 22 Sep 2022 21:34 UTC

In article <tgi721$2arjf$1@news.mixmin.net>, andrew wrote...
>
> I'm summing up time.
>
> Each column has a header of "Date", "Start" "End" & "Daily" time.
> Column A is the date (formatted as "Date").
> The next two columns are start & end times (formatted as "Time" "1:30 PM").
> Column D is the total for each day (formatted as "Time" "1:30").
> That's working.
>
> The total for all of them isn't working.
> That cell has the formula "=SUM(D2:D16)" but it's coming up too low.
> That cell (E2) is formatted as "Time" "1:30").
>
> This is so simple a task I can't believe the formula isn't working.
> Any suggestions?

That cell will be displaying as "time of day", not "hours". You can
*sometimes* add n hours to a 'time' field and get a time-of-day which is n
hours later, but only if that doesn't roll over midnight!

Date/time values are real numbers, with an integer part and a fractional part.
The integer part is used to signify the date (offset from some historic date
which I'd personally have to look up) and the fractional part (after the
decimal point) signifies the time. A cell formatted as Date will ignore the
fractional part, and the same cell formatted as Time will ignore the integer
part. When you start adding "times", then of course "14:00" plus "17:00"
appears as "07:00". Start by formatting all your cells as date/time and you'll
see what's going on. You could also add a column which references the values
in those cells but formatted as a number with a few decimal places, just to see
how this works.

Generally, it's best to use the built-in date/time manipulation functions
whenever you can. But for arithmetic, you may need to fool around with the raw
numbers. This article might help, but it doesn't go into how to deal with
values on different days (when INT() and MOD() are your friends):
https://bit.ly/3feEZ4P
--

Phil, London

computers / alt.comp.os.windows-10 / Re: Excel time addition formula isn't working

1
server_pubkey.txt

rocksolid light 0.9.81
clearnet tor