time and date fix

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,144
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
I have this data and need to fix the current and pervious date, the time is dinamic, every hour it will change but the range is for 24 hrs only. any way




Hourly_Founding_Day_Automation_V11.xlsm
ABC
5TimeAM/PMDay
612:00:00PM24
713:00:00PM24
814:00:00PM24
915:00:00PM24
1016:00:00PM24
1117:00:00PM24
1218:00:00PM24
1319:00:00PM24
1420:00:00PM24
1521:00:00PM24
1622:00:00PM24
1723:00:00PM24
1800:00:00AM25
1901:00:00AM25
2002:00:00AM25
2103:00:00AM25
2204:00:00AM25
2305:00:00AM25
2406:00:00AM25
2507:00:00AM25
2608:00:00AM25
2709:00:00AM25
2810:00:00AM25
2911:00:00AM25
Slide3-Working
Cell Formulas
RangeFormula
A6:A29A6=TEXT(E6,"hh:mm:ss")
B6:B29B6=TEXT(E6,"AM/PM")
C6:C29C6=IF(AND(A6<="23:00:00",B6="PM"),$H$3-1,$H$3)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
@JonRowland
here is the entire data.


Hourly_Founding_Day_Automation_V11.xlsm
ABCDEFGH
21724
31825
4
5TimeAM/PMDayPervious / Current DateHoursVOLUME 18-Jan_17-JanVOLUME 25-Jan_24-Jan
612:00:00PM2412:00 PM12:00#N/A49.92648538
713:00:00PM241:00 PM13:00#N/A55.06000971
814:00:00PM242:00 PM14:00#N/A58.98635164
915:00:00PM243:00 PM15:00#N/A57.7739256
1016:00:00PM244:00 PM16:00#N/A48.85911984
1117:00:00PM245:00 PM17:00#N/A48.09457288
1218:00:00PM246:00 PM18:00#N/A50.72837157
1319:00:00PM247:00 PM19:00#N/A58.09545668
1420:00:00PM248:00 PM20:00#N/A61.23532485
1521:00:00PM249:00 PM21:00#N/A66.34339219
1622:00:00PM2410:00 PM22:00#N/A66.55377174
1723:00:00PM2411:00 PM23:00#N/A62.71688337
1800:00:00AM2512:00 AM0:00#N/A71.65835492
1901:00:00AM251:00 AM1:00#N/A61.96093505
2002:00:00AM252:00 AM2:00#N/A47.41824025
2103:00:00AM253:00 AM3:00#N/A39.17838493
2204:00:00AM254:00 AM4:00#N/A32.58009683
2305:00:00AM255:00 AM5:00#N/A29.5150815
2406:00:00AM256:00 AM6:00#N/A30.59785313
2507:00:00AM257:00 AM7:00#N/A38.60906342
2608:00:00AM258:00 AM8:00#N/A32.31955249
2709:00:00AM259:00 AM9:00#N/A35.54122333
2810:00:00AM2510:00 AM10:00#N/A40.76150202
2911:00:00AM2511:00 AM11:00#N/A46.55700943
Slide3-Working
Cell Formulas
RangeFormula
G2:H2G2=G3-1
G3G3=H3-7
H3H3=Headers!N2
G5G5=IF(Report!D5=23,Report!E14,Report!D14)
H5H5=IF(Report!D5=23,Report!E15,Report!D15)
A6:A29A6=TEXT(E6,"hh:mm:ss")
B6:B29B6=TEXT(E6,"AM/PM")
C6:C29C6=IF(AND(A6<="23:00:00",B6="PM"),$H$3-1,$H$3)
E6:E28E6=IF(E7-TIME(1,0,0)<0, E7-TIME(1,0,0)+1, E7-TIME(1,0,0))
F6:F28F6=MOD(F7 - TIME(1,0,0), 1)
G6:G29G6=VLOOKUP($G$3&"_"&TEXT(E6,"hh:mm:ss AM/PM"),'Slide3-BD'!G:L,6,0)
H6:H29H6=VLOOKUP($H$3&"_"&TEXT(E6,"hh:mm:ss AM/PM"),'Slide3-BD'!G:L,6,0)
E29E29=G1
F29F29=G1
 
Upvote 0
I cannot test this because I cannot get your posted Excel data to load correctly, but try this formula in cell A6 and see if maybe if works...
=TEXT(E6,"[hh]:mm:ss")
 
Upvote 0
@Rick Rothstein

I tried the result, but no luck, as currently it is taking a lot of time almost 4 hrs in Manual process, were as every hr the data is populated,


I hope now you will be able to load the data correctly,
Book1
ABCDEFGH
1
21724
31825
4
5TimeAM/PMDayPervious / Current DateHoursVOLUME 18-Jan_17-JanVOLUME 25-Jan_24-Jan
623:00:00PM24this is correct11:00 PM23:00#N/A62.71688337
700:00:00AM25this is correct12:00 AM0:00#N/A71.65835492
801:00:00AM25this is correct1:00 AM1:00#N/A61.96093505
902:00:00AM25this is correct2:00 AM2:00#N/A47.41824025
1003:00:00AM25this is correct3:00 AM3:00#N/A39.17838493
1104:00:00AM25this is correct4:00 AM4:00#N/A32.58009683
1205:00:00AM25this is correct5:00 AM5:00#N/A29.5150815
1306:00:00AM25this is correct6:00 AM6:00#N/A30.59785313
1407:00:00AM25this is correct7:00 AM7:00#N/A38.60906342
1508:00:00AM25this is correct8:00 AM8:00#N/A32.31955249
1609:00:00AM25this is correct9:00 AM9:00#N/A35.54122333
1710:00:00AM25this is correct10:00 AM10:00#N/A40.76150202
1811:00:00AM25this is correct11:00 AM11:00#N/A46.55700943
1912:00:00PM24this should be 2512:00 PM12:00#N/A49.92648538
2013:00:00PM24this should be 251:00 PM13:00#N/A55.06000971
2114:00:00PM24this should be 252:00 PM14:00#N/A58.98635164
2215:00:00PM24this should be 253:00 PM15:00#N/A57.7739256
2316:00:00PM24this should be 254:00 PM16:00#N/A48.85911984
2417:00:00PM24this should be 255:00 PM17:00#N/A48.09457288
2518:00:00PM24this should be 256:00 PM18:00#N/A50.72837157
2619:00:00PM24this should be 257:00 PM19:00#N/A58.09545668
2720:00:00PM24this should be 258:00 PM20:00#N/A61.23532485
2821:00:00PM24this should be 259:00 PM21:00#N/A66.34339219
2922:00:00PM24this should be 2510:00 PM22:00#N/A66.55377174
Sheet1
Cell Formulas
RangeFormula
A6A6=TEXT(E6,"[hh]:mm:ss")
B6B6=TEXT(E6,"AM/PM")
C6:C29C6=IF(AND(A6<="23:00:00",B6="PM"),$H$3-1,$H$3)
 
Upvote 0
Sorry, I misunderstood what you were after. Remove the square brackets I suggested earlier and then try this. Calculate cell C6 using your current method if that really does what you want and then put this formula in cell C7 and copy it down...

=IF(A7="00:00:00",C6+1,C6)
 
Upvote 0
Rick Rothstein said:
Sorry, I misunderstood what you were after. Remove the square brackets I suggested earlier and then try this. Calculate cell C6 using your current method if that really does what you want and then put this formula in cell C7 and copy it down...

=IF(A7="00:00:00",C6+1,C6)
Click to expand...
its ok, np.
I will test and update you in next 2 hrs.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
181
cooleomter
C
V
  • Solved
vlookup not working in some case
Replies
4
Views
72
Peter_SSs
Peter_SSs
M
  • Question Question
Pivot table and calculation issues
Replies
1
Views
158
RoryA
RoryA
M
  • Question Question
Random SUM calculation issue in Pivot Table
Replies
1
Views
101
arthurbr
arthurbr
J
  • Question Question
IF statement with time and date.
Replies
8
Views
388
Cubist
Cubist

Forum statistics

Threads
1,226,061
Messages
6,188,642
Members
453,487
Latest member
LZ_Code

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top