Converting date and time to milliseconds in excel. (Certificate Bulkload).

Question 1

Hi Team,

How do I convert date and time to milliseconds in excel? I need to convert valid from and valid to in order to bulkload certificates in ServiceNow.

Question 2

Hi @Saheed ,

It will be good if you can share the example of excel sheet where you want to convert the date and time in Millisecond.

Below are two most used formula for same.

Try Changing it to A2, and also for milliseconds you need to multiply to 86400000

so formulae looks something like this:

(A2-DATE(1970,1,1))*86400000

Lets say you have your values in row 2 and your reference date in G1, then this formula

=((DATE(A2,B2,C2)+TIME(D2,E2,F2))-G1)*24*60*60

Please appreciate the efforts of community contributors by marking appropriate response as Mark my Answer Helpful or Accept Solution this may help other community users to follow correct solution in future.

Thanks

AJ

Linkedin Profile:- https://www.linkedin.com/in/ajay-kumar-66a91385/

ServiceNow Community Rising Star 2024

Question 3

Valid_from	Valid_to	Milliseconds
3/12/2024 23:59	4/12/2025 23:59
4/13/2023 23:59	5/13/2024 23:59

Question 4

Hi @Saheed ,

Thanks for sharing the details with me. Refer the below formula which help to get the Milliseconds.

Refer the below screenshot and formula of excel to convert the Date and Time in Milliseconds.

=(excel column-DATE(1970,1,1))*86400000

Information about the formula.

A1: This is the cell reference where your date and time value is located.
DATE(1970,1,1): This represents the Unix epoch, the starting point for Unix timestamps.
(A1 - DATE(1970,1,1)): This calculates the number of days between your date and the Unix epoch.
* 86400000: This converts the number of days to milliseconds. Since there are 86,400,000 milliseconds in a day (24 hours * 60 minutes * 60 seconds * 1000 milliseconds), we multiply by this factor.

Please appreciate the efforts of community contributors by marking appropriate response as Mark my Answer Helpful or Accept Solution this may help other community users to follow correct solution in future.

Thanks

AJ

Linkedin Profile:- https://www.linkedin.com/in/ajay-kumar-66a91385/

ServiceNow Community Rising Star 2024

Answer 1

Hi @Saheed ,

It will be good if you can share the example of excel sheet where you want to convert the date and time in Millisecond.

Below are two most used formula for same.

Try Changing it to A2, and also for milliseconds you need to multiply to 86400000

so formulae looks something like this:

(A2-DATE(1970,1,1))*86400000

Lets say you have your values in row 2 and your reference date in G1, then this formula

=((DATE(A2,B2,C2)+TIME(D2,E2,F2))-G1)*24*60*60

Please appreciate the efforts of community contributors by marking appropriate response as Mark my Answer Helpful or Accept Solution this may help other community users to follow correct solution in future.

Thanks

AJ

Linkedin Profile:- https://www.linkedin.com/in/ajay-kumar-66a91385/

ServiceNow Community Rising Star 2024

score 0 · Answer 2

Valid_from	Valid_to	Milliseconds
3/12/2024 23:59	4/12/2025 23:59
4/13/2023 23:59	5/13/2024 23:59

score 0 · Answer 3

Hi @Saheed ,

Thanks for sharing the details with me. Refer the below formula which help to get the Milliseconds.

Refer the below screenshot and formula of excel to convert the Date and Time in Milliseconds.

=(excel column-DATE(1970,1,1))*86400000

Information about the formula.

A1: This is the cell reference where your date and time value is located.
DATE(1970,1,1): This represents the Unix epoch, the starting point for Unix timestamps.
(A1 - DATE(1970,1,1)): This calculates the number of days between your date and the Unix epoch.
* 86400000: This converts the number of days to milliseconds. Since there are 86,400,000 milliseconds in a day (24 hours * 60 minutes * 60 seconds * 1000 milliseconds), we multiply by this factor.

Please appreciate the efforts of community contributors by marking appropriate response as Mark my Answer Helpful or Accept Solution this may help other community users to follow correct solution in future.

Thanks

AJ

Linkedin Profile:- https://www.linkedin.com/in/ajay-kumar-66a91385/

ServiceNow Community Rising Star 2024