Excel formula not calculating minutes correctly

[exceluser9]

Hi Team,

I have a data from column C to R

In column C i have put the value as 3, when i use this formula =SUMPRODUCT($C$2:$Q$2,C236:Q236)+R236
The value is showing as 24.63 instead of 25.03

Im facing this issue only for the data which is in decimals

Please could you help?

 

[awoohaw]

You need to provide at least a table of you data that is in the columns C and Q, and what is in R236.
An xl2bb add in mini sheet would be best assistance. To use it, please click the link below.

 

[exceluser9]

You need to provide at least a table of you data that is in the columns C and Q, and what is in R236.
An xl2bb add in mini sheet would be best assistance. To use it, please click the link below.

Hi There,

Here is the sample data

I have give the expected result in column G which i require but what im receiving is in column F when i use the formula in row 4 =SUMPRODUCT($B$3:$D$3,B4:D4)+E4

After 60 seconds the value is not changing to the next number. Any help is much appreciated

Column A	Column B	Column C	Column D	Column E	Column F	Column G
	Data entry	Adhoc	Verifying data	Meeting time	Received outcome	Expected result
	8.21	0.2	1.2
3/25/2024		3		30	30.6	31
3/26/2024	3				24.63	25.03
3/27/2024			3		3.6	4
3/28/2024	1	6	6		16.61	18.21

 

[Alex Blakenburg]

Please update your profile to show what version of Excel you are using.
This works for me but I have no way of testing whether it only works for me because I am using MS 365.

20240330 Time decimal to mins conversion exceluser9.xlsx
	A	B	C	D	E	F	G
1
2		Data entry	Adhoc	Verifying data	Meeting time	Received outcome	Expected result
3		8.21	0.2	1.2
4	25/03/2024		3		30	31	31
5	26/03/2024	3				25.03	25.03
6	27/03/2024			3		4	4
7	28/03/2024	1	6	6		18.21	18.21
Sheet1

Cell Formulas
Range	Formula
F4:F7	F4	=VALUE(TEXT(SUMPRODUCT(TIMEVALUE(SUBSTITUTE(TEXT($B$3:$D$3,"#0.00"),".",":")),B4:D4),"[h].mm"))+E4

 

[shift-del]

As an alternavtive.

=LET(
a,SUM(DOLLARDE(+$B$2:$D$2,60)/24/60*B3:D3),
b,MINUTE(a)+SECOND(a)%,
c,b+E3,
c)]

 

[Tetra201]

The following formula returns the expected results for the dataset in Post #3:

=DOLLARFR(SUMPRODUCT(DOLLARDE(+$B$3:$D$3,60),B4:D4)+DOLLARDE(E4,60),60)