Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If time Go to Function
I have a sheet where I need to input either an AM or Pm time in D2 and then
have it repopulate that time in B5 and add 1 hour showing AM or PM time. This can continue for up to 96 Hours. For some reason I cannot add an hour due to using the AM and PM in the hour. Any help would be greatly appreciated. A2: Enter Start 10:00am Hour Time Temp Time Hour Time Temp A5 1 B59:00 AM C5: 250 F5:17 H5:1:00 AM 250 33 5:00 PM 700 2 9:00 AM 250 18 2:00 AM 250 34 6:00 PM 750 3 11:00 AM 250 19 3:00 AM 250 35 7:00 PM 800 4 12:00 PM 250 20 4:00 AM 250 36 8:00 PM 800 5 1:00 PM 250 21 5:00 AM 250 37 9:00 PM 800 6 2:00 PM 250 22 6:00 AM 250 38 10:00 PM 800 7 3:00 PM 250 23 7:00 AM 250 39 11:00 PM 800 8 4:00 PM 250 24 8:00 AM 250 40 12:00 AM 800 9 5:00 PM 250 25 9:00 AM 300 41 1:00 AM 800 10 6:00 PM 250 26 10:00 AM 350 42 2:00 AM 800 11 7:00 PM 250 27 11:00 AM 400 43 3:00 AM 800 12 8:00 PM 250 28 12:00 PM 450 44 4:00 AM 850 13 9:00 PM 250 29 1:00 PM 500 45 5:00 AM 900 14 10:00 PM 250 30 2:00 PM 550 46 6:00 AM 950 15 11:00 PM 250 31 3:00 PM 600 47 7:00 AM 1000 16 12:00 AM 250 32 4:00 PM 650 48 8:00 AM 1050 Hour Time Temp Signature Hour Time Temp Signature Hour Time Temp Signature 49 9:00 AM 1100 65 1:00 AM 1500 81 5:00 PM 1500 50 10:00 AM 1150 66 2:00 AM 1550 82 6:00 PM 1000 51 11:00 AM 1200 67 3:00 AM 1600 83 7:00 PM 1000 52 12:00 PM 1250 68 4:00 AM 1650 84 8:00 PM OFF 53 1:00 PM 1300 69 5:00 AM 1700 85 9:00 PM 54 2:00 PM 1350 70 6:00 AM 1750 86 10:00 PM 55 3:00 PM 1400 71 7:00 AM 1800 87 11:00 PM 56 4:00 PM 1450 72 8:00 AM 1800 88 12:00 AM 57 5:00 PM 1500 73 9:00 AM 1800 89 1:00 AM 58 6:00 PM 1500 74 10:00 AM 1800 90 2:00 AM 59 7:00 PM 1500 75 11:00 AM 1800 91 3:00 AM 60 8:00 PM 1500 76 12:00 PM 1800 92 4:00 AM 61 9:00 PM 1500 77 1:00 PM 1800 93 5:00 AM 62 10:00 PM 1500 78 2:00 PM 1800 94 6:00 AM 63 11:00 PM 1500 79 3:00 PM 1800 95 7:00 AM 64 12:00 AM 1500 80 4:00 PM 1500 96 8:00 AM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If time Go to Function
I couldn't fully understand your table, but if you have 10:00 AM in D2, then
this formula =$D$2+(ROW()-4)*1/24 in B5 (formatted like "h:mm AM/PM") gives 11:00 AM, and dragged down in B6 12:00 AM, etc. Regards, Stefi €˛CAB€¯ ezt Ć*rta: I have a sheet where I need to input either an AM or Pm time in D2 and then have it repopulate that time in B5 and add 1 hour showing AM or PM time. This can continue for up to 96 Hours. For some reason I cannot add an hour due to using the AM and PM in the hour. Any help would be greatly appreciated. A2: Enter Start 10:00am Hour Time Temp Time Hour Time Temp A5 1 B59:00 AM C5: 250 F5:17 H5:1:00 AM 250 33 5:00 PM 700 2 9:00 AM 250 18 2:00 AM 250 34 6:00 PM 750 3 11:00 AM 250 19 3:00 AM 250 35 7:00 PM 800 4 12:00 PM 250 20 4:00 AM 250 36 8:00 PM 800 5 1:00 PM 250 21 5:00 AM 250 37 9:00 PM 800 6 2:00 PM 250 22 6:00 AM 250 38 10:00 PM 800 7 3:00 PM 250 23 7:00 AM 250 39 11:00 PM 800 8 4:00 PM 250 24 8:00 AM 250 40 12:00 AM 800 9 5:00 PM 250 25 9:00 AM 300 41 1:00 AM 800 10 6:00 PM 250 26 10:00 AM 350 42 2:00 AM 800 11 7:00 PM 250 27 11:00 AM 400 43 3:00 AM 800 12 8:00 PM 250 28 12:00 PM 450 44 4:00 AM 850 13 9:00 PM 250 29 1:00 PM 500 45 5:00 AM 900 14 10:00 PM 250 30 2:00 PM 550 46 6:00 AM 950 15 11:00 PM 250 31 3:00 PM 600 47 7:00 AM 1000 16 12:00 AM 250 32 4:00 PM 650 48 8:00 AM 1050 Hour Time Temp Signature Hour Time Temp Signature Hour Time Temp Signature 49 9:00 AM 1100 65 1:00 AM 1500 81 5:00 PM 1500 50 10:00 AM 1150 66 2:00 AM 1550 82 6:00 PM 1000 51 11:00 AM 1200 67 3:00 AM 1600 83 7:00 PM 1000 52 12:00 PM 1250 68 4:00 AM 1650 84 8:00 PM OFF 53 1:00 PM 1300 69 5:00 AM 1700 85 9:00 PM 54 2:00 PM 1350 70 6:00 AM 1750 86 10:00 PM 55 3:00 PM 1400 71 7:00 AM 1800 87 11:00 PM 56 4:00 PM 1450 72 8:00 AM 1800 88 12:00 AM 57 5:00 PM 1500 73 9:00 AM 1800 89 1:00 AM 58 6:00 PM 1500 74 10:00 AM 1800 90 2:00 AM 59 7:00 PM 1500 75 11:00 AM 1800 91 3:00 AM 60 8:00 PM 1500 76 12:00 PM 1800 92 4:00 AM 61 9:00 PM 1500 77 1:00 PM 1800 93 5:00 AM 62 10:00 PM 1500 78 2:00 PM 1800 94 6:00 AM 63 11:00 PM 1500 79 3:00 PM 1800 95 7:00 AM 64 12:00 AM 1500 80 4:00 PM 1500 96 8:00 AM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One-time NOW() function | Excel Worksheet Functions | |||
TIME function | Excel Worksheet Functions | |||
Time function | Excel Worksheet Functions | |||
Function to convert Time String to Time | Excel Worksheet Functions | |||
Help with Time Function | Excel Worksheet Functions |