I'm working between two tabs in Excel. One tab is for data entry and the other pulls the data using formulas. I'm trying to create a formula where it pulls 2 dates into 1 cell. The formula currently being used is 'Container Reference Sheet'!C60&" "&'Container Reference Sheet'!C61 but it doesn't pull up the date format. Instead it is pulling up a number format like 45134 & 45146. Thinking the formula needs to be changed to where it pulls up dates and not a number. How would I go about changing the formula?
1 Answer
Excel uses a number to keep track of date and time, 1.0 is a full 24h period and the integer portion is the number of days from the "epoch".
Type a 0 in a cell and set it to be a date - the epoch base date appears.
The actual date may vary, depending on the actual spreadsheet you use;
e.g. LibreOffice, "Old" or Mac Excel.
Now, to have two dates appear after each other in a single cell or even two adjacent cells; try using TEXT(...):
=TEXT(C61,"YYYY-MM-DD") & " - " & TEXT(C60,"YYYY-MM-DD")
The yyyy-mm-dd portion is the same codes
as can be used in the "Format cell"-dialog (CTRL+1).
With sheet references:
=TEXT('Container Reference Sheet'!C61,"YYYY-MM-DD") & " - " & TEXT('Container Reference Sheet'!C60,"YYYY-MM-DD")
-
So where would I add the above to my formula so it would pull from the other tab properly? Since the below didn't work: 'Container Reference Sheet'! TEXT("mm-dd-yy",C60)&" "&'Container Reference Sheet'! TEXT("mm-dd-yy",C61)– Ni.SenCommented Oct 19, 2023 at 15:15
-
When the cells are on a different sheet, include the sheet name in the CELL-reference– HannuCommented Oct 19, 2023 at 15:20