Refer to PART of a cell text to repeat information in another cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-2440054%22%20slang%3D%22en-US%22%3ERefer%20to%20PART%20of%20a%20cell%20text%20to%20repeat%20information%20in%20another%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440054%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20cell%20with%20custom%20format%3C%2FP%3E%3CP%3Emm%2Fdd%2Fyyyy%20hh%3AmmAM%2FPM%3C%2FP%3E%3CP%3EAnd%20another%20cell%20with%20a%20different%20time%20in%20it%20formatted%3C%2FP%3E%3CP%3Ehh%3AmmAM%2FPM%3C%2FP%3E%3CP%3EFor%20a%20data%20upload%2C%20I%20need%20that%20second%20cell%20to%20repeat%20the%20date%20from%20the%20first%20cell%20so%20that%20it%20has%20the%20exact%20same%20format%20as%20the%20first%20cell.%20Is%20there%20a%20way%20to%20refer%20to%20%3CEM%3Ejust%20the%20portion%20of%20the%20cell%3C%2FEM%3E%20that%20I%20need%20and%20repeat%20that%20formula%20in%20all%20of%20the%20second%20cells%3F%3C%2FP%3E%3CP%3EI%20guess%20that%20I%20can%20copy%20the%20first%20column%20and%20change%20the%20format%20to%20only%20the%20date%20and%20then%20use%20%3DCONCAT(A2%2CB2)%20or%20%3DA2%26amp%3B%22%20%22%26amp%3BB2%20but%20it's%20a%20lot%20more%20work%20in%20this%20instance.%20AND%20upon%20testing%20it%2C%20it%20is%20losing%20the%20date%20formatting%20and%20reading%20the%20date%20as%20a%20division%20problem...%20so%20that's%20not%20working%20either.%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20could%20help%20me%20trouble%20shoot%20either%20of%20those%20solutions%2C%20I%20will%20send%20you%20cookies!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EBest%2C%3C%2FP%3E%3CP%3EAmanda%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2440054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have a cell with custom format

mm/dd/yyyy hh:mmAM/PM

And another cell with a different time in it formatted

hh:mmAM/PM

For a data upload, I need that second cell to repeat the date from the first cell so that it has the exact same format as the first cell. Is there a way to refer to just the portion of the cell that I need and repeat that formula in all of the second cells?

I guess that I can copy the first column and change the format to only the date and then use =CONCAT(A2,B2) or =A2&" "&B2 but it's a lot more work in this instance. AND upon testing it, it is losing the date formatting and reading the date as a division problem... so that's not working either.    

If anyone could help me trouble shoot either of those solutions, I will send you cookies!

Best,

Amanda

1 Reply

@BravoCampbell 

Amanda, in Excel dates are actually integers numbers starting from Jan 01, 1900 which is equal to 1. Time is decimal part of the number, 1 hour is actually the 1/24 value. Format is only helps to show them in more human friendly form.

 

Thus I'd work with values, don't care about formats. If you have datetime value the date only will be =INT(A1) and the time only will be =MOD(A1,1). You may apply to the result any desired format.

 

Above is just basics, not sure what exactly you'd like to receive. Maybe small sample file ?