Date / Time Help

[davin]

Hello All,

I have a question to ask and I've searched and can't seem to locate anything relevant so here goes. I'll give it my best to ask clear, detailed question.

I have a db that is from a wfm(workforce management) table. This table has the date and time stored in a field called Start_Moment.
This Start_Moment field, per the software companies definition is this: the number of minutes that have passed since December 30, 1899, 12:00 AM GMT.
so data in a field looks like 55,196,880
How can I convert this to a normal time and date, like 11/16/2005 14:30 ?

Alternatively,

I have been able to pull that down into a date, but there are multiple instances of this date in the source data. that said, can someone show me how to make cell B1 and C1 join to display and read as 11/15/2005 8:00 AM?

I have tried Concatenate however it displays the data as a numerical value.

Book1
	A	B	C	D
1		11/15/2005	8:00AM
2
3
4
5
6
7
8
Sheet1

Any help would be greatly appreciated.

Regards,
Davin

 

[Zack Barresse]

Hi there,

1) =(A1/1440)+DATE(1900,1,1)-1

Where this would be GMT, and assuming your time (minutes) number is in A1. If you want local time, you need to factor that in as well. Remember, Excel see's a single (24 hour) day as one whole number; so 12 hours would be 0.5. Example: I'm on the west coast (US), I am GMT - 8, so I would take away 8 hours (-8/24).


2) =B1+C1


HTH

 

[davin]

Okay, your formula worked GREAT! with one exception its off by 1 day. when I run it for today it shows tomorrows date (11/17/2005)

What am I missing,

I have this exactly in cell A3, where H3 is the date in minutes. Accounting for CST

=(H3/1440)+DATE(1900,1,1)-(6/24)

I can't believe the second option was that simple! Thanks!

 

[Zack Barresse]

That's what the "-1" part was for. Excel will not recognize a date prior to 1900, and since "Dec 31, 1899 12:00 AM" is exactly one day prior to that, we must minus one whole numeric, else you will be off by one day.

So it shouuld be ..

=(H3/1440)+DATE(1900,1,1)-1-(6/24)

 

[davin]

WORKS GREAT!!!!

Thanks for your help. Saved me weeks of time.

Davin

 

[barry houdini]

Would this give you the same result?

=H3/1440-0.25

 

[davin]

Just tried it, that works as well! thanks, but what relevance is the 0.25 ?

-Davin

 

[barry houdini]

0.25 is the equivalent of 6/24, you could use either

 

[Zack Barresse]

Would this give you the same result?

=H3/1440-0.25

Doh! You don't need the date, as it's 1. Silly me. Thanks Barry.

Btw, if you end up using the 1904 date system, it should only be a difference of 1462 days.

 

[barry houdini]

Doh! You don't need the date, as it's 1. Silly me. Thanks Barry.

Btw, if you end up using the 1904 date system, it should only be a difference of 1462 days.

Hi Zack

OK, I may be missing the point.....(it wouldn't be the first time )

Are you saying that including the date is effective? If you're using 1904 date system then

=(H3/1440)+DATE(1900,1,1)-1-(6/24)

gives #NUM! I believe

Perhaps to cope with either date system

=H3/1440-0.25-ISERROR(DATE(1900,1,1))*1462