Creating a Formula to TextJoin

Shazir

Banned - Rules violations
Joined
Jul 28, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Hi, I hope you all are doing well.

I was looking for a formula that will solve the below picture Example.

I want to get the Cells in "Late" Column if C2,D2,E2 have such format "Late - 11:00 AM" then "Late" should be converted with Date as available in C1,D1,E1 and time should be as it is.

and result would be as = 8/1/2020 - 11:00 AM



1598524889692.png
 
I'm trying to make sense of that formula, see if this works

=SUBSTITUTE(TRIM(IF(Sheet1!C3>TIMEVALUE("08:30 AM"),"Late"&" - "&TEXT(Sheet1!C3,"HH:MM AM/PM")&" ; ","")& IF(Sheet1!D3<>TIMEVALUE("00:00:00"),"Early"&" - "&TEXT(Sheet1!D3,"HH:MM AM/PM")&" ; ","")& IF(Sheet1!E3<>"","Extra Shift ; ","")&IF(Sheet2!C3="A","Absent ; ","")&IF(Sheet2!D3<>TIMEVALUE("00:00:00"),"Short"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM")&" ; ",""))& IF(Sheet2!E3<>TIMEVALUE("00:00:00"),"Half"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM"),"")&";",";;","")
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Thank you so much i made it what i want by your help and earlier post. thank you so much.
 
Upvote 0
I got one more problem that is to get the Late status as well as Early as well as Extra Shift by this formula

=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(LEFT(C2:AG2,4)="Late",TEXT(C$1:AG$1+RIGHT(C2:AG2,8),"mm/dd/yyyy - (hh:mm AM/PM)"),"")) )

View attachment 21252
 
Upvote 0
That looks like the formula that @Peter_SSs provided for you to use in excel. It is probably not compatible with google sheets.
 
Upvote 0
It also gives an error on 365. Because there is not space in yellow highlighted ;Half - 01:00 PM; and this pattern is not similar with 4th row.
 

Attachments

  • 1598628587521.png
    1598628587521.png
    14.5 KB · Views: 7
Upvote 0
That is because the formula is based on the example that you provided which had a single description and time in the cell, now that you have changed it to multiple descriptions and times in the cell the formula can not work, it is not designed to work with that.
 
Upvote 0
There can be any solution for this one i would really appreciate for the help.

Might there can be issue of adjustment of ; which is not giving similar result as you can see in yellow highlighted cell and in the cell after this both are not giving similar result.

=SUBSTITUTE(TRIM(IF(Sheet1!C3>TIMEVALUE("08:30 AM"),"Late"&" - "&TEXT(Sheet1!C3,"HH:MM AM/PM")&" ; ","")& IF(Sheet1!D3<>TIMEVALUE("00:00:00"),"Early"&" - "&TEXT(Sheet1!D3,"HH:MM AM/PM")&" ; ","")& IF(Sheet1!E3<>"","Extra Shift ; ","")&IF(Sheet2!C3="A","Absent ; ","")&IF(Sheet2!D3<>TIMEVALUE("00:00:00"),"Short"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM")&" ; ",""))& IF(Sheet2!E3<>TIMEVALUE("00:00:00"),"Half"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM"),"")&";",";;","")

1598629657290.png
 
Upvote 0
You could try changing RIGHT(C2:AG2,8) to MID(C2:AG2,8,8) but I'm not convinced that it will work well.

Extracting values from a cell that contains mixed information will always be a bad idea.

Why are there different formats to the data in column C? Maybe the problem is with the formula there?
 
Upvote 0
Thanks let me check it but i am also requesting to review the below formula to give correct result of semicolon which result is different in yellow highlighted cell Where it is in start and end of the last text.

and in third row semicolons sequence is correct.


=SUBSTITUTE(TRIM(IF(Sheet1!C3>TIMEVALUE("08:30 AM"),"Late"&" - "&TEXT(Sheet1!C3,"HH:MM AM/PM")&" ; ","")& IF(Sheet1!D3<>TIMEVALUE("00:00:00"),"Early"&" - "&TEXT(Sheet1!D3,"HH:MM AM/PM")&" ; ","")& IF(Sheet1!E3<>"","Extra Shift ; ","")&IF(Sheet2!C3="A","Absent ; ","")&IF(Sheet2!D3<>TIMEVALUE("00:00:00"),"Short"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM")&" ; ",""))& IF(Sheet2!E3<>TIMEVALUE("00:00:00"),"Half"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM"),"")&";",";;","")
 
Upvote 0
I didn't realise that the formula I changed earlier was in there, looking at it again I found one semicolon was missing and one bracket was in the wrong place, this should fix it.

=SUBSTITUTE(TRIM(IF(Sheet1!C3>TIMEVALUE("08:30 AM"),"Late"&" - "&TEXT(Sheet1!C3,"HH:MM AM/PM")&" ; ","")&IF(Sheet1!D3<>TIMEVALUE("00:00:00"),"Early"&" - "&TEXT(Sheet1!D3,"HH:MM AM/PM")&" ; ","")&IF(Sheet1!E3<>"","Extra Shift ; ","")&IF(Sheet2!C3="A","Absent ; ","")&IF(Sheet2!D3<>TIMEVALUE("00:00:00"),"Short"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM")&" ; ","")&IF(Sheet2!E3<>TIMEVALUE("00:00:00"),"Half"&" - "&TEXT(Sheet2!E3,"HH:MM AM/PM")&" ; ",""))&";",";;","")
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top