have a cell display what another cell displays, not the numerical value

[kranic3]

So im making a timesheet for work. I have one worksheet displaying Time Start and Time Finish for each shift. This is entered for example as 8:00:00 AM and the cell will display 8:00 AM (for start time) and 4:00:00PM displayed as 4:00 PM (for time finished)

I then have another cell that is supposed to show "8:00 AM - 4:00PM"

I have tried using:
C3 &" - " &D3
but it shows as a numerical time value "0.333333333 -"

Anyone know what im doing wrong?

[AliGW]

Please attach the workbook.

[azumi]

Maybe like this:

8:00:00AM is formatted as time

4:00:00PM is formatted as text

Just check with right click your mouse both of C3 and D3, If you combine value with text formatted, yes you can, but if there is data with time formatted, the'll showed up as number...

[cbatrody]

Hi,

Please try the following:

=CONCATENATE(TEXT(C3,"H:MM AM/PM"),"-",TEXT(D3,"H:MM AM/PM"))

[cffndncr]

Use the =TEXT() function.

Assume your 8:00 AM is in A2 and your 4:00 PM is in B2, your formula would be =TEXT(A2,"hh:mm AM/PM")&" - "&TEXT(B2,"hh:mm AM/PM")

[benishiryo]

welcome to the forum, kranic3. the above solutions will work for you, but let me address your question of what you are doing wrong. Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day). so 8 am is 0.333333333; 1/3 of a day. that's why you need the TEXT formula as suggested

[kranic3]

thanks guys, that did it (=CONCATENATE(TEXT(C3,"H:MM AM/PM"),"-",TEXT(D3,"H:MM AM/PM"))

is there a way for me to use that entire formula within another in an if/then statement to print OFF if no time is entered?

thanks again

[cbatrody]

Hi,

Yes, you can do that:

=IF(OR(C3="",D3=""),"OFF",CONCATENATE(TEXT(C3,"H:MM AM/PM"),"-",TEXT(D3,"H:MM AM/PM")))

[kranic3]

last question guys, and i really do appreciate all this help.

Any way to add another IF/THEN to subtract .5h for lunch on shifts 5 hours and over?

[cbatrody]

Hi,

Please find the attached file (sample template) for subtracting shifts over 5 hours.

Regards,
Chandra




Please click on ‘* Add Reputation’ if this was helpful

[kranic3]

Sorry, i should have been more specific.
I have attached my file. In Sheet2 in the totals column (P) is there a way to create an addition to the formula that will subtract .5h off of each day in that week with a value of 5 or larger.

Im not asking you to do the work for me either (if this is coming across rude) I am more than willing to put in time on this, i am just struggling with a bit :P

[cbatrody]

What you mean by "with a value of 5 or larger. "? Does it mean that if somebody is present for 5 or more days in a week then 0.5 hours need to be deducted from each day?

[kranic3]

i need to have .5hrs subtracted from each day that is 5 hours or longer (preferably without creating a new column, but just adding that to the current totaling formula)

[cbatrody]

Please find attached the updated file.

Regards,
Chandra


Please click on ‘* Add Reputation’ if this was helpful

[cbatrody]

An alternate simpler way to calculate the total hours..

[benishiryo]

another way:
=(SUMIF($B$2:$O$2,"Out",B3:O3)-SUMIF($B$2:$O$2,"In",B3:O3))*24-SUMPRODUCT(((($B$2:$O$2="Out")*(B3:O3)-($B$2:$O$2="In")*(B3:O3))>=5/24)*0.5)