0

I'm looking for a way to concatenate two time period values in one Excel 2010 cell. This is a Excel sheet for my office, in which my working hours should be listed. Beside other values, which are not relevant for this, there are four columns:
Column A contains the datevalue.
Column B contains the start time for this date.
Column C contains the end time for this date.
Column D contains the time period for this date.

Until now, it's a very simple sum of the time period values in the fields above.

=SUM(D$5:D$26)

which is formatted as [hh]:mm --> e.g. "168:00"

I want to improve this, to show up with additional information of overtime / undertime... and I expect it to look like "180:30 (+12:30)" or "155:15 (-12:45)"

I tried to concatenate it like this

=SUM(D$5:D$26) & " (" & SUM(D$5:D$26;-(COUNTIF(D$5:D$26;"*")*1/3)) & ")"

but it doesn't work out :( Currently, my formula returns for my two example dates with 10:00 and 10:30 worktime "0,854166666666667 (-5,8125)"

To calculate the current over-/undertime, I wanted to sum up all days, which have a value in the worktime period, and multiply it by the daily work period (8 hours). This seems to be wrong in my formula, but I could not find the error :(

I do not know, how to format the substrings before the concatenation... do you have any idea?

Matt
  • 45,022
  • 8
  • 78
  • 119
narrenblut
  • 1
  • 1

2 Answers2

0

Formula:

=TEXT(SUM(D$5:D$26),"[h]:mm") & " (" & TEXT(SUM(D$5:D$26;-(COUNTIF(D$5:D$26;"*")*1/3)),"[h]:mm)" & ")"

The problem is that Excel only displays a time is a time field, the actual value is a number. If you want to get the text representation, you must convert it to text before the concatenate.

Community
  • 1
  • 1
Mr. Mascaro
  • 2,693
  • 1
  • 11
  • 18
0

If it's just the formatting you're having trouble with then this should get it to look the way you want. I don't understand the calculation you're trying to perform though.

=TEXT(SUM(D$5:D$26),"HH:MM") & " (" & TEXT(SUM(D$5:D$26,-(COUNTIF(D$5:D$26,"*")*1/3)),"HH:MM") & ")"

CactusCake
  • 986
  • 3
  • 12
  • 32
  • The calculation should represent the current setpoint (e.g. if I have to work 8 hours per day and it's the third working day in the current month, then this should be 24 hours. I want to count all the "days" (rows), in which I have set any timeperiod. – narrenblut Nov 01 '14 at 12:28