convert number to date format

[Phill032]

Hi Guys,

Hopefully someone can help convert the below number into a date format.
18222
This is supposed to correspond to the 20 Nov 2017.
The full data extracted from the report is all in one single cell and each number represents a date seperated by a full stop. I just need the last date. example below

18167. 18167. 18174. 18202. 18213. 18222

 

[FDibbins]

OK I must be missing something here, how on earth does 18222 correspond to 20 11 2017?

 

[Phill032]

If i knew that i wouldn't be here
Each number represents the date of a price change on the internet of stocked items.
This is the data that my reports pull out, was hoping someone would have seen something similar and have a answer..

 

[FDibbins]

I get that you need an answer, but that is in no date format that I am familiar with. Excel treats dates as a number of days from 0/1/1900, so todays date of 11/26/2017 (US format) is actually 43065


So, with that said, do you have any idea what 18222 might represent? For instance, 18222 days ago was Saturday, January 06, 1968

 

[Phill032]

Yep you are right into it, it is days since the 01/01/68... just had this confirmed from our service provider.
So it is easy to work with now that i know what the numbers relate too.

Thanks for your help

 

[Dave Patton]

Excel 2010
	A	B
1	31-Dec-67	24837
2
3	18222	20-Nov-17
4	18223	21-Nov-17
5
6a

Cell Formulas
Range	Formula
B1	=A1
B3	=A3+$B$1
B4	=A4+$B$1

If the date sequence starts at Dec 31 1967 (general format 24837),

adding 24837 to the 18222 will yield the specified date.

 

[FDibbins]

OK that makes sense then. Happy to help and thanks for the feedback

 

[Marcelo Branco]

Trying to guess
It seems that the in this system the number 1 corresponds to 1 Jan 1968.
So to get the Excel corresponded date all you have to do is add 24837 (corresponds to 31 Dec 1967)

Something like

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Starting Date​

[/td][td]

31/12/1967​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Number​

[/td][td]

Excel Date​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

18167​

[/td][td]

26/09/2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

18174​

[/td][td]

03/10/2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

18202​

[/td][td]

31/10/2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

18213​

[/td][td]

11/11/2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

18222​

[/td][td]

20/11/2017​

[/td][/tr]
[/table]

Formula in A4 copied down
=A4+B$1

A shot in the dark

M.

 

[Phill032]

Nice, not the way i was going to go about it but i think this would be better than my way

thanks again