Merging Excel Date and Time into one Cell

Helping your colleagues in office to sort out their problems always presents an opportunity for you to learn something new and a sense of satisfaction as well. Recently I experienced that in my office. After finding the solution to the problem, I felt that there might be many such people who would be facing this problem. That is the precise reason for this post.

My colleague had his data in Excel sheet in which he had captured date and time of a particular event in two separate cells. Just like cells B5 and C5 in the example below. He wanted the difference in two dates in terms of number of days and number of hours. 

In this example, calculating the difference in time between the two events is not possible until and unless Date and Time are combined in one cell. This was achieved in the following manner:

1) Firstly data in column B was formatted into date format as DD/MM/YYYY format, which is commonly followed format in India. Date 1 was 25th April 2012 and Date 2 was 27th April 2012.


2) Data in column C was formatted in Time format as hh:mm format.   


3) Now with date in cell B5 and time in cell C5, both were combined into one cell in cell D5 using the formula 


=INT(B5)+MOD(C5,1)


4) After that data in cell D5 was formatted into "Custom" format as "dd/mm/yyyy hh:mm:ss" format.


5) Same thing was done for date 2 as well.


6) To calculate the number of days between the two dates, formula was entered in cell D10 as =D8-D5 and D10 cell was formatted in number format without decimal and 1000 separator.


7)  To calculate the time difference between the two dates, formula was entered in cell D11 as =D8-D5 and cell D11 was formatted in "Custom" format as "[h]:mm"

If you click on any cell with formula above, formula can be viewed in bottom right corner. Please keep in mind that Excel date and time formats are governed by regional settings of your machine and as a result, formats may appear different. However, logic remains the same. 


Also on some machines, you may not be able to view the above embedded excel sheet directly. On such machines, you can click on that window to open it directly in Google Drive/Google docs.

Excel - Check Value Within a Set of Numbers and then use Conditional Formula

Yesterday, one of my friends called me and asked for a help in Excel. Basically he had a dump exported in Excel from SAP finance module and in that dump, there were more than 1,50,000 line items. In SAP, all debit amounts are positive (+) figures and all credit amounts are negative (-) figures. But the problem my friend faced was that when he exported them into Excel, all the figures were positive figures only, but each figure was associated with a posting key, which was a determining factor to decide whether it was debit or credit i.e. whether (+) or (-). 

And those determining posting keys were in combinations of (+,-) like (40,50), (89,99) etc depending on transaction type. There were 3-4 pairs, he had at present, but in future that number was going to increase. He wanted an excel formula to change the amount to either (+) or (-) depending on the posting key. 

I started to look for the possible ways to do it. I tried to use the "IF" function in combination with excel functions like "MATCH", "VLOOKUP", "LOOKUP", "OR", but wasn't getting the desired results. And since the number of pairs were uncertain, I intended to give him a future ready solution. 

Finally I was sucessful in acheiving the desired results using "IF" and "COUNTIF" functions. The solution was as under with imaginary figures and imaginary sets of posting keys. 

Please feel free to export the above sheet into Excel and look for the formula used in Column F. You may also click on any cell in Column F above, but the formula used won't be visible completely, unless you scroll down in formula field. If you accidentally edit the document, just reload the page and the original document would be visible again. 

Even if the combinations increase in future, the codes need to be added in the columns A and B and range needs to be extended in the formula. 

Any better solution is always welcome. Its a constant learning process.