MS-Excel stores the dates by counting up from January 1, 1900. Excel understands March 18, 2008 8:36 AM to be 39525.3587. To see how MS-Excel reads dates type the date with time into a cell (A1) then right-click on the cell and choose Format>>Category >>Number (4 decimal places). To convert the date back choose format Date/Time and choose the format you prefer.
Place the time/date March 28, 2008 8:45 in another cell (B1) and right-click to format the cell so it shows just the time.
To subtract the two enter into cell C1 =B1-A1. Notice the result is a decimal number (Excel calculates the time by subtracting 39325.3646-39525.3587). To show the difference in minutes choose Format Cells>>Category: Custom and under Type: type in [mm] which will show the result rounded to the nearest minute. In the scenario above the result should be 08.
Sometimes database programmers store dates in different formats. Some have the date in one field and the time in another (so you are downloading just the time) while others will have the two combined. To separate out the time from the date in cell A1 remember that Excel reads the date as the numbers to the left of the decimal place and the time as the numbers to the right of it. March 28, 2008 8:36 AM reads as March 29,2008=39525 and 8:36 AM as 0.3587. To separate out the numbers to the right of the decimal (convert a date/time to just a time) use the MOD function in excel. In another cell (D1) type =MOD(A1,1) which finds the remainder to a number after dividing by 1. In effect it converts 39525.3587 to 0.3587. Then set the format of cell D1 as Custom>>[mm].
I hope that helps trying to subtract dates/times in excel. If you run into a specific problem post it in the comments section and I’ll try to help.
Posts
No comments:
Post a Comment