# Date / Time Help

#### davin

##### New Member
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
ABCD
111/15/20058:00AM
2
3
4
5
6
7
8
Sheet1

Any help would be greatly appreciated.

Regards,
Davin

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

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!

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)

WORKS GREAT!!!!

Thanks for your help. Saved me weeks of time.

Davin

Would this give you the same result?

=H3/1440-0.25

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

-Davin

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

barry houdini said:
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.

firefytr said:
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

Replies
5
Views
625
Replies
3
Views
284
Replies
15
Views
381
Replies
22
Views
504
Replies
4
Views
411

1,203,456
Messages
6,055,544
Members
444,795
Latest member
cjohnson333

### 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.

### Which adblocker are you using?

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

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