If the timestamp is a true date-time, =INT(A2) gives the date and =MOD(A2,1) gives the time. If it is text such as 05/01/2024 14:32:10, split it at the space with Text to Columns, or build each part with DATE and TIMEVALUE.
First: is it a number or text?
Type =ISNUMBER(A2). TRUE means Excel holds a date-time number. Underneath, the whole part counts days and the fraction counts the time of day. Say 05/01/2024 14:32:10 is stored as about 45296.6057: 45296 is 5 January 2024, and 0.6057 is the share of the day gone by 14:32:10, because 52,330 seconds out of 86,400 is 0.6057. FALSE means text, and you need the text route.
True date-times: INT and MOD
- Date: =INT(A2) then format the cell as dd/mm/yyyy.
- Time: =MOD(A2,1) then format the cell as hh:mm:ss.
- Hour on its own: =HOUR(A2), useful for a late-night versus daytime split.
INT throws away the fraction, leaving midnight of that day. MOD(A2,1) is the remainder after dividing by one, which is exactly the fraction. The two add back to the original.
Text timestamps: three ways
- Text to Columns. Select the column, choose Data, Text to Columns, Delimited, tick Space only, and set the first output column to Date, DMY. You get the date in one column and the time text in the next. Choose an empty destination first so you do not overwrite the original.
- Formulas by position, for text in the fixed form dd/mm/yyyy hh:mm:ss. Date: =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2)). Time: =TIMEVALUE(MID(A2,12,8)). Characters 1 to 10 are the date, character 11 is the space and 12 to 19 are the time.
- Power Query. Load the column, change its type to Date/Time using locale English (India), then add Date, Date Only and Time, Time Only columns from the Transform tab.
Why does this matter for daily totals?
This is the practical reason to split them. Suppose you want the total spent on 5 January and your timestamps include a time. A formula that compares the column to the date 05/01/2024 only matches stamps at exactly midnight. Rows at 14:32 are skipped, so the day looks emptier than it was.
Two ways to total correctly. With the date in a helper column B, which is =INT(A2):
- =SUMIFS($C$2:$C$500,$B$2:$B$500,E1) where E1 holds 05/01/2024.
- Without a helper: =SUMIFS($C$2:$C$500,$A$2:$A$500,">="&E1,$A$2:$A$500,"<"&E1+1), which covers the whole day from midnight to just before the next midnight.
Say there are three debits that day, ₹640 at 09:10, ₹1,250 at 14:32 and ₹300 at 21:05. The helper version returns 2,190, as 640 plus 1,250 plus 300. The naive equality version returns nothing at all.
Two formatting surprises catch people out. After =MOD(A2,1) the cell may show 0.6057 until you apply Custom hh:mm:ss. And if you ever add up durations beyond 24 hours, use the format [h]:mm:ss, since plain hh wraps around at midnight.
To compare times without rounding trouble, convert to whole seconds since midnight: =ROUND(MOD(A2,1)*86400,0). For 14:32:10 that returns 52,330, a number you can compare, sort and subtract safely.
Handy extras once the time is separate
- Count the late-night spends with =COUNTIFS($D$2:$D$500,">="&TIME(22,0,0)) on a time column.
- Group by the hour with =HOUR(A2) and a pivot table.
- Not every statement carries a time. Many give only a date, so first check what yours includes before building the sheet around it.
Not every statement carries a time, and some carry the time only for card or UPI rows. If the time column comes out as 0:00:00 for many rows, the source simply had no time, so do not build an hourly analysis on it.
Check list
- Confirm =ISNUMBER on the date and time columns.
- Make sure the date column's smallest and largest values match the statement period.
- Keep the original timestamp column until the totals agree with the statement.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

